Clash Royale CLAN TAG

SQL merge minimum time difference

I'm having trouble with a SQL statement that is a bit over my skill level. Running this in a DB2 datawarehouse.

I need to join two columns (CODE1 and CODE2) from TABLE2 into TABLE1 based on some IDs and the minimum time difference between a date in TABLE1 (STARTDATE) and a date in TABLE2 (TIME_SENT). The statement below shows what I'm trying to do, but having issues with ordering of group by and having clause.

group by

having

SELECT *
FROM TABLE1
LEFT JOIN (SELECT B.ID1, B.ID2, D.CODE1, D.CODE2
FROM TABLE1 B, TABLE2 D
WHERE D.STATUS = '7'
GROUP BY B.ID1, B.ID2
HAVING ABS(B.STARTDATE - D.TIME_SENT) = MIN(ABS(B.STARTDATE - D.TIME_SENT)) TABLE2
ON TABLE1.ID1 = TABLE2.ID1
AND TABLE1.ID2 = TABLE2.ID2;


Appreciate any help with this.

STRUCTURE TABLE1:

---------------------------------------------------------
| ID1 (VARCHAR) | ID2 (VARCHAR) | STARTDATE (TIMESTAMP) |
---------------------------------------------------------


STRUCTURE TABLE2:

----------------------------------------------------------------------------------------------------------------
| ID1 (VARCHAR) | ID2 (VARCHAR) | TIME_SENT (TIMESTAMP) | STATUS (INTEGER) | CODE1 (VARCHAR) | CODE2 (VARCHAR) |
----------------------------------------------------------------------------------------------------------------


STRUCTURE OUTPUT:

---------------------------------------------------------------------------------------------
| ID1 (VARCHAR) | ID2 (VARCHAR) | STARTDATE (TIMESTAMP) | CODE1 (VARCHAR) | CODE2 (VARCHAR) |
---------------------------------------------------------------------------------------------


So TABLE2 can have several different code pairs (CODE1,CODE2) per unique ID (ID1,ID2), but I only want to keep the code pair where TABLE2.TIME_SENT is closest to TABLE1.STARTDATE.

TABLE2

(CODE1,CODE2)

(ID1,ID2)

TABLE2.TIME_SENT

TABLE1.STARTDATE

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.