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...
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.
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 |
+--------------+----------+----------+
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
