CODEWITHSUNDEEP
sqlrsql-server
Mel
Mel

Reputation: 17

How to subset the readmitted cases from an inpatients’ table to calculate the total length of stay of the readmitted cases in SQL Server 17?

I am working with an inpatients' data table that looks like the following:

ID      | AdmissionDate |DischDate     |LOS |Readmitted30days 
+------+-------+-------------+---------------+---------------+
|001    | 2014-01-01    | 2014-01-12    |11 |1
|101    | 2014-02-05    | 2014-02-12    |7  |1
|001    | 2014-02-18    | 2018-02-27    |9  |1
|001    | 2018-02-01    | 2018-02-13    |12 |0
|212    | 2014-01-28    | 2014-02-12    |15 |1
|212    | 2014-03-02    | 2014-03-15    |13 |0
|212    | 2016-12-23    | 2016-12-29    |4  |0
|1011   | 2017-06-10    | 2017-06-21    |11 |0
|401    | 2018-01-01    | 2018-01-11   |10  |0
|401    | 2018-10-01    | 2018-10-10   |9   |0

I want to create another table from the above in which the total length of stay (LOS) is summed up for those who have been readmitted within 30 days. The table I want to create looks like the following:

ID      |Total LOS   
+------+-----------
|001    |39
|212    |28 
|212    |4
|1011   |11 
|401    |10 
|401    |9

I am using SQL Server Version 17. Could anyone help me do this? Thanks in advance

Upvotes: 1

Views: 59

Answers (3)

Charlieface
Charlieface

Reputation: 71374

The Readmitted30days column seems irrelevant to the question and a complete red herring. What you seem to want is to aggregate rows which are within 30 days of each other.

This is a type of gaps-and-islands problem. There are a number of solutions, here is one:

  • We use LAG to check whether the previous DischDate is within 30 days of this AdmissionDate
  • Based on that we assign a grouping ID by doing a running count
  • Then simply group by ID and our grouping ID, and sum
  • The dates and LOS don't seem to match up, so I've given you both
WITH StartPoints AS (
    SELECT *,
      IsStart = CASE WHEN
          DATEADD(day, -30, AdmissionDate) <
          LAG(DischDate) OVER (PARTITION BY ID ORDER BY DischDate)
          THEN 1 END
    FROM YourTable
),
Groupings AS (
    SELECT *,
      GroupId = COUNT(IsStart) OVER (PARTITION BY ID ORDER BY DischDate ROWS UNBOUNDED PRECEDING)
    FROM StartPoints
)
SELECT
  ID,
  TotalBasedOnDates = SUM(DATEDIFF(day, AdmissionDate, DischDate)),  -- do you need to add 1 within the sum?
  TotalBasedOnLOS = SUM(LOS)
FROM Groupings
GROUP BY ID, GroupID;

db<>fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You want to use aggregation:

select id, sum(los)
from t
group by id
having max(Readmitted30days) = 1;

This filters after the aggregation so all los values are included in the sum.

EDIT:

I think I understand. Every occasion where Readmitted30days = 0, you want a row in the result set that combines that row with the following rows up to the next matching row.

If that interpretation is correct, you can construct groups using a cumulative sum and then aggregate:

select id, sum(los)
from (select t.*,
             sum(1 - Readmitted30days = 0) over (partition by id order by admissiondate) as grp
      from t
     ) t
group by id, grp;

Upvotes: 0

eshirvana
eshirvana

Reputation: 24568

if I understand correctly :

select Id, sum(LOS)
from tablename
where Readmitted30days = 1
group by Id

Upvotes: 0

Related Questions