CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Fred Morten Solbakken
Fred Morten Solbakken

Reputation: 33

How many patients has been readmitted within 30 days from their first stay

we need to find out how many (and who it is) of our patients who has been readmitted within 30 days of their first stay.

Table (very simplified): patientID (int), DateInToHospital (datetime), DateOutOfHospital (Datetime).

So we need all patient where DateInToHospital (second stay) <= DateOutOfHospital (first stay) + 30 days

(We use MS SQL Server 2008 R2)

Upvotes: 0

Views: 989

Answers (2)

SqlZim
SqlZim

Reputation: 38023

This will return the earlier admits of a patient who is re-admitted within 30 days from any admittance:

select *
from p
where exists ( /* re-admit within 30 days */
  select 1
  from p i
  where i.PatientId = p.PatientId
    and i.AdmitDate > p.DischargeDate
    and i.AdmitDate <= dateadd(day,30,p.DischargeDate)
    )

This will return the initial admittance of a patient who is re-admitted within 30 days from their first admittance:

select *
from p
where exists ( /* re-admit within 30 days */
    select 1
    from p i
    where i.PatientId = p.PatientId
      and i.AdmitDate > p.DischargeDate
      and i.AdmitDate <= dateadd(day,30,p.DischargeDate)
      )
  and not exists ( /* only for the first admittance */
    select 1
    from p i
    where i.PatientId = p.PatientId
      and i.AdmitDate < p.AdmitDate
      )

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415705

This should work:

WITH FirstAdmit AS (
    SELECT patientID, MIN(DateInToHospital) FirstStayStart
    FROM [Table]
), 
FirstDischarge AS (
     SELECT t.patientID, DateOutOfHospital, FirstStayStart
     FROM FirstAdmit a
     INNER JOIN [Table] t ON t.patientID = a.patientID AND t.DateInToHospital = a.FirstStayStart
)
SELECT t.*
FROM [Table] t
INNER JOIN FirstDischarge d on t.patientID = d.patientID 
    AND t.DateInToHospital <= DATEADD(d, 30, d.DateOutOfHospital) 
    AND t.DateInToHospital <> FirstStayStart

And here's a version using APPLY that might be a bit faster:

SELECT t.* 
FROM [Table] t
CROSS APPLY (
   SELECT TOP 1 DateInToHospital, DateOutOfHospital FROM [Table] m WHERE m.patientID = t.patientID ORDER BY DateInToHospital
) FirstStay
WHERE t.DateInToHospital <= DATEADD(d, 30, FirstStay.DateOutOfHospital)
    AND t.DateInToHospital <> FirstStay.DateInToHospital

Upvotes: 0

Related Questions