Comparing 2 different dates per person/member

I am trying to pull data comparing two dates per person, i.e. discharge date to the next admin date for each patient within 30 days. Also, there could be multiple discharge dates and admin dates.

Pt Name PT Number Admin Date Discharge Date ----------------------------------------------------- John Doe 55 01/02/2023 01/12/2023 John Doe 55 01/31/2023 02/05/2023 John Doe 55 05/28/2023 06/01/2023 John Doe 55 06/15/2023 06/17/2023 

This patient should be counted twice because he was readmitted (01/31/2023) within 30 days of prior discharged (01/12/2023); and readmitted (06/15/2023), discharged (06/01/2023).

Great thanks!

I am expecting a field for Readmissions that output the total of readmission (30 days).

I don't know where to begin. I have created a stored procedure but that is it.

1 Answer

we can use table value function cross apply and some correlation to get the records which fall wtihin 30 days of eachother.

note if you have multiple dates which all fall within 30 you're count will inflate because the 1st one will include hte next 2, the next one will include the next one so you get 2 on the first admin date, and 1 on the 2nd admin date. See as an example. you didn't specify how you want this handled.

You can also switch my cross apply to an ourter apply as in my above link to see how the base dataset relates to the applied data set to get the "within 30 day" records.

Demo

Create table T1 (PtName varchar(20), PTNumber numeric, AdminDate date, DischargeDate date) Insert into t1 values ('John Doe', 55, '01/02/2023', '01/12/2023'), ('John Doe', 55, '01/31/2023', '02/05/2023'), ('John Doe', 55, '05/28/2023', '06/01/2023'), ('John Doe', 55, '06/15/2023', '06/17/2023') 

Option 1) if you want the rows which are "Readmissions"

Select Z.* FROM T1 A Cross Apply (Select * from T1 B where B.PtNumber = A.PtNumber and DATEADD(day, 30, A.DischargeDate) >= B.AdminDate and A.AdminDate < B.AdminDate) Z 

Option 2) if you just want the count of readmissions by Patient

Select count(*) readmissions, Z.PTName, Z.PtNumber FROM T1 A Cross Apply (Select * from T1 B where B.PtNumber = A.PtNumber and DATEADD(day, 30, A.DischargeDate) >= B.AdminDate and A.AdminDate < B.AdminDate) Z Group by Z.PTName, Z.PTNumber 

Gives us:

Result 1)

+----------+----------+------------+---------------+ | PtName | PTNumber | AdminDate | DischargeDate | +----------+----------+------------+---------------+ | John Doe | 55 | 2023-01-31 | 2023-02-05 | | John Doe | 55 | 2023-06-15 | 2023-06-17 | +----------+----------+------------+---------------+ 

Result 2)

+--------------+----------+----------+ | readmissions | PTName | PtNumber | +--------------+----------+----------+ | 2 | John Doe | 55 | +--------------+----------+----------+