Merging two tables in sql with specific conditions: sql server

Viewed 22

I have these two datasets:

DF1:

PT_ID Hospital_ID Admit_Dt Discharge_DT Discharge_ID
001 ABC 01-01-2021 01-03-2021 001,ABC,01-01-2021,01-03-2021
001 ABC 01-10-2021 01-15-2021 001,ABC,01-10-2021,01-15-2021

DF2:

PT_ID ICU_ID ICU_Admit_Dt ICU_Discharge_DT Service_Code
001 XYZ 01-19-2021 01-19-2021 ICU

Desired:

PT_ID Hospital_ID Admit_Dt Discharge_DT Discharge_ID Service_Code
001 ABC 01-01-2021 01-03-2021 001,ABC,01-01-2021,01-03-2021 ICU
001 ABC 01-10-2021 01-15-2021 001,ABC,01-10-2021,01-15-2021 NULL

Details: The first table has information on a patients hospital in-patient visits. The 2nd table has information on whether an inpatient visit led to an ICU visit. I would like to create a third table tracking if patients were admitted to the ICU within 30 days of their in-patient discharge date. In the case above, the patient had 2 in-patient visits (DF1) and both discharges for those visits were within 30 days of their 1 ICU visit (DF2). I would like that ICU visit to only count for one of their initial in-patient visits (it does not matter which one if both are within 30 days).

The code I currently have is this:

select distinct
                d.PT_ID,
                d.Hospital_ID,
                d.Admit_Dt,
                d.Discharge_DT,
                d.Discharge_ID,
                t.Service_Code,
from #df1 d
                left join #df2 t
                        on t.PT_ID = d.PT_ID and t.ICU_Admit_Dt between d.Discharge_DT and 
                        DATEADD(day, 30, d.Discharge_DT)
order by PT_ID

What is currently happening is that the ICU visit is being counted for each of the initial discharges which is leading to an over count: (see below):

Current Output:

PT_ID Hospital_ID Admit_Dt Discharge_DT Discharge_ID Service_Code
001 ABC 01-01-2021 01-03-2021 001,ABC,01-01-2021,01-03-2021 ICU
001 ABC 01-10-2021 01-15-2021 001,ABC,01-10-2021,01-15-2021 ICU
0 Answers
Related