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

Answer 1

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