如何在SQL Server 17中从住院患者表筛选30天内再入院病例并计算其总住院时长（LOS）

First, let's recap your data and desired output for clarity:

Original Admission Data #

Desired Output #

Solution Query #

The core idea is to group admissions for the same patient that are within a 30-day window of each other, then sum their LOS values. We'll use window functions to identify these groups:

-- Replace YourTableName with the actual name of your table
WITH RankedAdmissions AS (
    SELECT 
        ID,
        AdmissionDate,
        DischDate,
        LOS,
        -- Calculate days between current admission and previous discharge
        DATEDIFF(day, LAG(DischDate) OVER (PARTITION BY ID ORDER BY AdmissionDate), AdmissionDate) AS DaysSinceLastDischarge,
        -- Flag if this admission starts a new group (first admission or >30 days since last discharge)
        CASE 
            WHEN LAG(DischDate) OVER (PARTITION BY ID ORDER BY AdmissionDate) IS NULL THEN 1
            WHEN DATEDIFF(day, LAG(DischDate) OVER (PARTITION BY ID ORDER BY AdmissionDate), AdmissionDate) > 30 THEN 1
            ELSE 0
        END AS IsNewGroup
    FROM YourTableName
),
GroupedAdmissions AS (
    SELECT 
        ID,
        LOS,
        -- Generate a unique group ID for each cluster of admissions
        SUM(IsNewGroup) OVER (PARTITION BY ID ORDER BY AdmissionDate ROWS UNBOUNDED PRECEDING) AS GroupID
    FROM RankedAdmissions
)
SELECT 
    ID,
    SUM(LOS) AS [Total LOS]
FROM GroupedAdmissions
GROUP BY ID, GroupID
ORDER BY ID, GroupID;

How This Works #

1. RankedAdmissions CTE:

   • We partition the data by ID and sort admissions by AdmissionDate to process each patient's records in chronological order.
   • LAG(DischDate) fetches the discharge date of the patient's previous admission, and we calculate the gap between that date and the current admission using DATEDIFF.
   • We set IsNewGroup to 1 if this is the patient's first admission, or if more than 30 days have passed since their last discharge (signaling a separate, unrelated admission episode).

2. GroupedAdmissions CTE:

   • We use a running total of IsNewGroup to assign a unique GroupID to each cluster of admissions that fall within 30 days of each other. Each time IsNewGroup is 1, the group ID increments, creating a new cluster.

3. Final Aggregation:

   • We group by ID and GroupID, then sum the LOS values to get the total length of stay for each admission cluster.

This logic matches your desired output: for example, patient 212's first two admissions are only 18 days apart (from discharge to next admission), so they're grouped together for a total LOS of 28. Their 2016 admission is far enough from the previous one to be its own group with a total LOS of 4.

内容的提问来源于stack exchange，提问作者Mel