Teradata newbee here. I have a requirement where I need to find the blood test result that happened prior to the patients doctor visit.
Could you please help?
For eg: Patient X had a blood test done on 11/2/2016,12/1/2016/12/31/2016 and had visited the doctor on 11/10/2016,11/11/2016,1/1/2017,so what I am looking for is
Many thanks in advance!
This is how I usually approach this scenario:
SELECT patient_id, dt AS visit_date, -- find the most recent test_date from the tests table Max(CASE WHEN flag = 0 THEN dt end) Over (PARTITION BY patient_id ORDER BY dt, flag ROWS Unbounded Preceding) AS test_date FROM ( -- combine both tables, visits & tests and add a flag indicating the table SELECT patient_id, test_date AS dt, 0 AS flag FROM tests UNION ALL SELECT patient_id, visit_date AS dt, 1 AS flag FROM visits ) AS dt -- only rows from the visits table QUALIFY flag = 1