Need help in sql to get the value closest to visit date.

General

Need help in sql to get the value closest to visit date.

Hi All,

 

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

11/2/2016 11/10/2016

11/2/2016 11/11/2016

12/31/2016 1/1/2017

 

Many thanks in advance!

2 REPLIES
Junior Contributor

Re: Need help in sql to get the value closest to visit date.

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

 

Re: Need help in sql to get the value closest to visit date.

Thank you!