Hospital readmission history in SQL Server

Question

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

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:

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?

Can use STRING_AGG aggregate function to build the concatenated diagnosis history if you are using SQL Server 2017, otherwise will have to use a correlated subquery with `FOR XML` option. — EzLo, Dec 27 '18 at 15:15
Are you trying to get history for display? If this is a display, you probably don't want to concat a string in SQL. Format the display of multiple records in your output code. — Shawn, Dec 27 '18 at 15:23
And will this be done for just one patient, or will you be looking at multiple patients? — Shawn, Dec 27 '18 at 15:25

score 3 · Answer 1 · answered Dec 27 '18 at 15:14

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).

score 1 · Answer 2 · answered Dec 27 '18 at 15:20

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

Hospital readmission history in SQL Server

2 Answers2