this is an interesting task, i think this is only possible using some recursion.
For this you need some starting point and your narration confuses me a bit...
I would start with the most recent FU_CALL_DT for the most recent DISCHARGE_DT to match the expected result.
But recursive queries in Teradata have some limitations like no OLAP/aggregates in the recursive part, so it's down to a SP using a loop like this:
CREATE VOLATILE TABLE vt, NO LOG AS tab WITH NO DATA ON COMMIT PRESERVE ROWS;
INSERT INTO vt
FROM tab AS k
WHERE NOT EXISTS
SELECT * FROM vt
WHERE vt.PAT_NAME = k.PAT_NAME
AND (vt.DISCHARGE_DT = k.DISCHARGE_DT OR vt.FU_CALL_DT = k.FU_CALL_DT)
OVER (PARTITION BY PAT_NAME
ORDER BY DISCHARGE_DT DESC, FU_CALL_DT DESC) = 1;
UNTIL ACTIVITY_COUNT = 0
When both tables have a matching PI on PAT_NAME this should be fast.
Regarding NULLs, you didn't tell how to account for :-)
Thanks for the solution. Due to the way our IT is set up we have not been able to use stored procedures. However, I am going to pursue this as it would open up other opportunities for us. Meanwhile, I will either have to resolve this using functions with arrays in Crystal Reports or see if the client will accept some other logic.