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 |