SQLite function to combine tables with min() and max() function with dates?-CodePudding

In SQLite, I am trying to combine both tables. Specifically, I am trying to find a way to combine lab result dates with 0-7 days of follow-up for diagnosis dates (minimum 0 day, like the same day, to maximum 7 days). I have attached the tables here (note: not real ID, ENCID, lab result date, and diag_date numbers). Is there a possible way to combine both tables without the first row (of Table 1) attached to DIAG_DATE of 11/19/2020 in SQLite? If not, what about in Python?

Table 1

ID  ENCID     LAB RESULT DATE 
1      098    10/29/2020
1      098    11/17/2020    
1      098    11/15/2020
1      098    11/12/2020
1      098    11/19/2020

Table 2

ID  ENCID      DIAG_DATE
1     098      11/19/2020
1     098      10/01/2021

My goal: Table 3

ID   ENCID    LAB_RESULT_DATE     DIAG_DATE
1      098    11/12/2020          11/19/2020
1      098    11/15/2020          11/19/2020 
1      098    11/17/2020          11/19/2020
1      098    11/19/2020          11/19/2020

Here is my SQLite code below (I am aware this is not right):

CREATE TABLE table3 AS
SELECT *
FROM table1
JOIN table2
WHERE table1.ID=table2.ID AND table1.ENCID=table2.ENCID AND DIAG_DATE >= LAB_RESULT_DATE
HAVING MAX(DIAG_DATE)>MIN(LAB_RESULT_DATE)
ORDER BY table1.ID ASC

CodePudding user response：

you can join both table with thier ENCID and dates.

You need to chech if the time frame of the second ON parameter is enough, to caputure all dates and times else you need to adjust the time by adding , '-10 seconds' for example

SELECT t1.*, t2."DIAG_DATE"
FROM tab1 t1 JOIN tab2 t2 ON t1."ENCID" = t2."ENCID" AND "LAB RESULT DATE" BETWEEN     DATE("DIAG_DATE",
        '-7 day') AND "DIAG_DATE"

ID     ENCID   LAB RESULT DATE         DIAG_DATE
1      98      2020-11-17 01:00:00     2020-11-19 01:00:00
1      98      2020-11-15 01:00:00     2020-11-19 01:00:00
1      98      2020-11-12 01:00:00     2020-11-19 01:00:00
1      98      2020-11-19 01:00:00     2020-11-19 01:00:00

db<>fiddle here