SQL: Gaps and Islands, Grouped dates

Question

I am trying to group dates within 3 days of each other and assign points based on readmission's within 30 days. A MRN would receive 3 points per readmission. Any help on modifying my query below would be great.

Example:

CREATE TABLE #z      (
    ID INT IDENTITY(1,1), 
    OrganizationMrn INT,
    VisitDate DATE, 
    CATEGORY VARCHAR(15) )

INSERT #z(OrganizationMrn, VisitDate, CATEGORY)
VALUES 
(1, '1/2/2016','Inpatient'),
(1, '1/5/2016','Inpatient'),  
(1, '1/7/2016','Inpatient'),  
(1, '1/8/2016','Inpatient'), 
(1, '1/9/2016','Inpatient'),  
(1, '2/4/2016','Inpatient'), 
(1, '6/2/2016','Inpatient'),
(1, '6/3/2016','Inpatient'),
(1, '6/5/2016','Inpatient'),  
(1, '6/6/2016','Inpatient'), 
(1, '6/8/2016','Inpatient'),  
(1, '7/1/2016','Inpatient'),  
(1, '8/1/2016','Inpatient'),  
(1, '8/4/2016','Inpatient'),  
(1, '8/15/2016','Inpatient'), 
(1, '8/18/2016','Inpatient'), 
(1, '8/28/2016','Inpatient'),
(1, '10/12/2016','Inpatient'),
(1, '10/15/2016','Inpatient'),
(1, '11/17/2016','Inpatient'),
(1, '12/20/2016','Inpatient')

Desired Output: I really only need the Actual Visits, OrganizationMrn, and Points. (When dates are grouped(Actual Visits), the first date should be used for readmission within 30 days ).

ACTUAL Visits   Grouped Dates               Re-admissions       Points
1/2/2016        (grouped 1/2, 1/5)
1/7/2016        (grouped 1/7, 1/8, 1/9)     Readmit from 1/2    (3 points)  
2/4/2016                                    Readmit from 1/7    (3 points)
6/2/2016        (grouped 6/2, 6/3, 6/5)
6/6/2016        (grouped 6/6, 6/8)          Readmit from 6/2    (3 points)  
7/1/2016                                    Readmit from 6/6    (3 points)
8/1/2016        (grouped 8/1, 8/4)
8/15/2016       (grouped 8/15, 8/18)        Readmit from 8/1    (3 points)  
8/28/2016                                   Readmit from 8/15   (3 points)
10/12/2016      (grouped 10/12, 10/15)
11/17/2016  
12/20/2016  
___________________________________________ 6 total readmits    (18 total points)

The query below uses gaps and islands to group days within 3 days of each other. However if the dates are consecutive, the start/ end dates are grouped.(Example: The query below groups, [1/2, 1/5, 1/7/, 1/8, 1/9] into one row; the dates should be split into two rows [1/2, 1/5] and [1/7/, 1/8, 1/9]).

Once the grouped dates have individual rows I need to assign 3 points to each readmission within 30 days. (Actual Visit per OrganizationMrn within 30 days of each other). The desired output section above describes how the dates in my example should be grouped.

;WITH StartingPoints AS (
    SELECT OrganizationMrn, VisitDate, ROW_NUMBER() OVER (ORDER BY VisitDate) AS Sequence FROM #z AS A 
    WHERE a.category = 'Inpatient' AND NOT EXISTS (
        SELECT * FROM #z AS B 
        WHERE B.OrganizationMrn = A.OrganizationMrn AND 
              B.VisitDate >= DATEADD(DAY, -4, A.VisitDate) AND
              B.VisitDate < A.VisitDate AND
              B.Category = 'Inpatient'  )   ),
EndingPoints AS (
    SELECT OrganizationMrn, VisitDate, ROW_NUMBER() OVER (ORDER BY VisitDate) AS Sequence FROM #z AS A 
    WHERE a.category = 'Inpatient' AND NOT EXISTS (
        SELECT * FROM #z AS B 
        WHERE B.OrganizationMrn = A.OrganizationMrn AND 
              B.VisitDate <= DATEADD(DAY, 4, A.VisitDate) AND 
              B.VisitDate > A.VisitDate AND
              B.Category = 'Inpatient'  )   )
SELECT S.OrganizationMrn, S.VisitDate AS StartDate, E.VisitDate AS EndDate, CEILING((DATEDIFF(DAY, S.VisitDate, E.VisitDate) + 1) / 4.0) AS Points
FROM StartingPoints AS S 
    JOIN EndingPoints AS E ON (E.Sequence = S.Sequence)
ORDER BY S.OrganizationMrn DESC

1/5 isn't grouped with 1/7 because 1/2 is the actual visit for 1/5 (the first date in a group is the actual visit date). 1/7 is not within 3 days of 1/2 so 1/7 would be a new visit. Does that make sense? — JBritton, Sep 21 '16 at 13:13
So any visitDate that doesn't have another visitDate in the previous 3 days is the start of an island, and the first visitDate more than 3 days after that is the start of another island, even if there was a visitDate less than 3 days prior, is that it? — Tab Alleman, Sep 21 '16 at 13:16
6/5 & 6/6 question: 6/2 would be the start of a new visit, it then groups visits within 3 days of the start (6/2, 6/3, 6/5 into one using the 1st date as the actual visit date). 6/6 is considered a new visit because it is not with 3 days of 6/2. — JBritton, Sep 21 '16 at 13:20
Are you on Version 2012 or greater? If so then you can make use of the LAG() or LEAD() windowing functions. — Ross Bush, Sep 21 '16 at 13:37
Two questions: When you say readmission is an admission within 1 month of the previous admission, you mean "1 month" or "30 days"? Second, if you group Jan-02,Jan-03, Jan-05 as a group, and another admission occur on Feb-03. Does the Feb-03 count as readmission? (What I mean here is the point of start counting, is the "1 month" or "30 days" start counting from the first day or the last day of the previous group?) — DVT, Sep 21 '16 at 14:08
1. 30 days, not one month. 2. The 30 days would count from the first day of the previous set of "grouped" dates. So in the example, Jan. 2, 3, & 5 would be grouped, and Feb. 3 would not be a readmission because it is >30 days past Jan. 2. — JBritton, Sep 21 '16 at 16:59

score 2 · Accepted Answer · answered Sep 21 '16 at 19:00

This answer works for the example you provided. It might be useful if you have small tables and limited admission. (It use recursion on the dates).

WITH a AS (
    SELECT
        z1.VisitDate
        , z1.OrganizationMrn
        , (SELECT MIN(VisitDate) FROM #z WHERE VisitDate > DATEADD(day, 3, z1.VisitDate)) AS NextDay
    FROM
        #z z1
    WHERE
        CATEGORY = 'Inpatient'
), a1 AS ( 
    SELECT
        OrganizationMrn
        , MIN(VisitDate) AS VisitDate
        , MIN(NextDay) AS NextDay
    FROM
        a
    GROUP BY
        OrganizationMrn
), b AS (
    SELECT
        VisitDate
        , OrganizationMrn
        , NextDay
        , 1 AS OrderRow
    FROM
        a1


    UNION ALL

    SELECT
        a.VisitDate
        , a.OrganizationMrn
        , a.NextDay
        , b.OrderRow +1 AS OrderRow
    FROM
        a
        JOIN b
        ON a.VisitDate = b.NextDay
), c AS (
SELECT
    VisitDate
    , (SELECT MAX(VisitDate) FROM b WHERE b1.VisitDate > VisitDate) AS PreviousVisitDate
FROM
    b b1
)
SELECT
    c1.VisitDate
    , CASE 
        WHEN DATEDIFF(day,c1.PreviousVisitDate,c1.VisitDate) < 30 THEN PreviousVisitDate
        ELSE NULL
     END AS ReAdmissionFrom
    , CASE
        WHEN DATEDIFF(day,c1.PreviousVisitDate,c1.VisitDate) < 30 THEN 3
        ELSE 0
    END AS Points
FROM
    c c1

SQL: Gaps and Islands, Grouped dates

1 Answers1

Linked

Related