Comparing timestamps for closest match

[kdbenson]

I have two tables, Tests and Orders. There is not a direct relationship between a distinct test and a distinct order. The most granular link is Encounter_ID and there can be numerous tests and orders for a particular encounter. I need to create a list of all the tests with any associated orders. The best way to do this is to match the Entry_Time of a test with the order that has the closest Order_Time for that Encounter_ID. Sample data might look like this:

TESTS
Patient Encounter_ID Entry_Time
Wilson, Tom 1 12/2/2008 22:35
Wilson, Tom 1 12/3/2008 0:32
Wilson, Tom 1 12/3/2008 2:24
Wilson, Tom 1 12/3/2008 9:20
Wilson, Tom 1 12/3/2008 22:18
Wilson, Tom 2 12/7/2008 2:59
Wilson, Tom 2 12/7/2008 4:37
Wilson, Tom 2 12/7/2008 7:41
Wilson, Tom 2 12/7/2008 9:57
Jenner, Cindy 3 12/1/2008 7:53
Jenner, Cindy 3 12/1/2008 12:28
Jenner, Cindy 3 12/2/2008 7:55
Jenner, Cindy 3 12/3/2008 11:16
Wither, Alex 4 12/2/2008 19:14


ORDERS
Encounter_ID Order_Time
1 12/2/2008 22:14
1 12/3/2008 2:28
2 12/7/2008 4:00
3 12/1/2008 7:33


The result set should look like this:

OUTPUT
Patient Encounter_ID Entry_Time Order_Time
Wilson, Tom 1 12/2/2008 22:35 12/2/2008 22:14
Wilson, Tom 1 12/3/2008 0:32
Wilson, Tom 1 12/3/2008 2:24 12/3/2008 2:28
Wilson, Tom 1 12/3/2008 9:20
Wilson, Tom 1 12/3/2008 22:18
Wilson, Tom 2 12/7/2008 2:59
Wilson, Tom 2 12/7/2008 4:37 12/7/2008 4:00
Wilson, Tom 2 12/7/2008 7:41
Wilson, Tom 2 12/7/2008 9:57
Jenner, Cindy 3 12/1/2008 7:53 12/1/2008 7:33
Jenner, Cindy 3 12/1/2008 12:28
Jenner, Cindy 3 12/2/2008 7:55
Jenner, Cindy 3 12/3/2008 11:16
Wither, Alex 4 12/2/2008 19:14

I think the answer lies in using an OLAP function, but I am new to these. I came up with this using RANK, but it didn't quite work:

SELECT PATIENT, ENCOUNTER_ID, ENTRY_TIME, ORD_TIME
FROM (
Select PATIENT, tst.ENCOUNTER_ID, ENTRY_TIME,
Case
When r_order = 1 Then ORDER_TIME
Else Null
End As ord_time,
abs((entry_time - order_time) day(4) To minute) As time_diff,
rank() over (Partition By tst.ENCOUNTER_ID, ORDER_TIME
Order By time_diff asc) As r_order

From TESTS tst
Left Join ORDERS ord
On tst.encounter_id = ord.encounter_id
) q1
Group By 1,2,3,4
Order By encounter_id, entry_time, ord_time

Unfortunately, this gives me duplicates if there are more than one order for an encounter:

PATIENT ENCOUNTER_ID entry_time ord_time
Wilson, Tom 1 2008-12-02 22:35:00.000000 NULL
Wilson, Tom 1 2008-12-02 22:35:00.000000 2008-12-02 22:14:00.000000
Wilson, Tom 1 2008-12-03 00:32:00.000000 NULL
Wilson, Tom 1 2008-12-03 02:24:00.000000 NULL
Wilson, Tom 1 2008-12-03 02:24:00.000000 2008-12-03 02:28:00.000000
Wilson, Tom 1 2008-12-03 09:20:00.000000 NULL
Wilson, Tom 1 2008-12-03 22:18:00.000000 NULL
Wilson, Tom 2 2008-12-07 02:59:00.000000 NULL
Wilson, Tom 2 2008-12-07 04:37:00.000000 2008-12-07 04:00:00.000000
Wilson, Tom 2 2008-12-07 07:41:00.000000 NULL
Wilson, Tom 2 2008-12-07 09:57:00.000000 NULL
Jenner, Cindy 3 2008-12-01 07:53:00.000000 2008-12-01 07:33:00.000000
Jenner, Cindy 3 2008-12-01 12:28:00.000000 NULL
Jenner, Cindy 3 2008-12-02 07:55:00.000000 NULL
Jenner, Cindy 3 2008-12-03 11:16:00.000000 NULL
Wither, Alex 4 2008-12-02 19:14:00.000000 NULL

I don't want lines 1 & 4 as that information is contained in lines 2 & 5. I don't know if I am on the right track with this approach or should be trying something different. It seems like there should be a better solution out there. Any help would be greatly appreciated.

I am using Teradata, but I think the SQL above should all be ANSI-SQL compliant. I apologize if it is not.

Thanks,
Kevin

 

[kdbenson]

Here is a solution that works. However, in reality the query requires a number of other tables with # of records > 1 million so I am concerned about performance. I would still appreciate any suggestions you have to make this more efficient.

-Kevin

SELECT ts.patient, ts.encounter_id, ts.entry_time, q1.order_time, q1.time_diff, q1.r_order
FROM TESTS ts
LEFT JOIN (
SELECT PATIENT, tst.ENCOUNTER_ID, ENTRY_TIME, ORDER_TIME,
abs((entry_time - order_time) day(4) To minute) As time_diff,
rank() over (Partition By tst.ENCOUNTER_ID, ORDER_TIME
Order By time_diff asc) As r_order
From TESTS tst
Left Join ORDERS ord
On tst.encounter_id = ord.encounter_id
Qualify r_order = 1
) q1
ON ts.ENCOUNTER_ID = q1.ENCOUNTER_ID
AND ts.ENTRY_TIME = q1.ENTRY_TIME
Order By ts.encounter_id, ts.entry_time, q1.order_time