CODEWITHSUNDEEP
sqlsql-server
Monte
Monte

Reputation: 113

Hospital readmission history in SQL Server

I have a table which holds hospital admission date, discharge date and the diagnosis:

enter image description here

Now I am trying to get count of readmission within last one year and the history for each encounter (Discharge date + Diagnosis). The outcome should looks something like this:

enter image description here

I am able to get the count but struggling with the history. This is my code:

SELECT
    A.Encounter, A.Patient, A.AdmissionDt, A.DischargeDt, 
    (SELECT COUNT(*) 
     FROM MyTable B 
     WHERE A.Patient = B.Patient 
       AND B.AdmissionDt >= DATEADD(YY, -1, A.DischargeDt) 
       AND B.AdmissionDt < A.DischargeDt) AS Cnt 
FROM
    MyTable A 
ORDER BY
    AdmissionDt DESC

Can I get some help please?

Upvotes: 2

Views: 427

Answers (2)

Anson Aricatt
Anson Aricatt

Reputation: 393

Can you try this query 

SELECT A.encounter, 
       A.patient, 
       A.admissiondt, 
       A.dischargedt, 
       (SELECT Count(*) 
        FROM   mytable B 
        WHERE  A.patient = B.patient 
               AND B.admissiondt >= Dateadd(yy, -1, A.dischargedt) 
               AND B.admissiondt < A.dischargedt) AS Cnt, 
        (SELECT  convert (varchar,b.admissiondt) +' - ' + b.diagnosis +'
        '
        FROM   mytable B
        WHERE  A.patient = B.patient 
               AND B.admissiondt >= Dateadd(yy, -1, A.dischargedt) 
               AND B.admissiondt < A.dischargedt  for xml path ('')) as history 
FROM   mytable A 
ORDER  BY admissiondt DESC

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

You can get both of your desired columns from correlated subqueries.

The count column would simply be a COUNT(*) of rows for the same patient before the current row but within the last year.

The History would be a FOR XML concatenation of the same set of rows as the count (before the current row but within the last year).

Upvotes: 3

Related Questions