SQL: spaces and islands, grouped dates

Question

I try to group dates within 3 days of each other and assign points based on readmission for 30 days. MRN will receive 3 points for re-admission. Any help on modifying my request 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 outcome: I really only need actual visits, OrganizationMrn and Points. (When the 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 following request uses gaps and islands to group days within 3 days of each other. However, if the dates are consecutive, the start and end dates are grouped. (Example: query below groups, [1/2, 1/5, 1/7 /, 1/8, 1/9] in one line; dates should be divided into two lines [1/2, 1/5] and [1/7 /, 1/8, 1/9]).

Once grouped dates have separate lines, I need to assign 3 points for each readmission for 30 days. (The actual visit to the organization within 30 days with each other). The desired output section above describes how dates should be grouped in my example.

 ;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 on the example you provided. This can be useful if you have small desks and limited access. (It uses recursion in 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