SQL: Recursive CTE by grouped date range

Question

I am fairly new to SQL and I am having trouble assigning points to patients based on their visit date. I think a Recursive CTE is the best way to achieve this but I cannot wrap my head around actually writing it.

Each OrganizationMrn should be assigned 1 point for each visit EXCEPT when there has been another visit within the past 3 days. If the patient has multiple visits in 3 days, one point should be assigned.

Any help modifying my query to a recursive cte that assigns points as described would be greatly appreciated.

Examples:
A patient has these 7 visits: 1/1, 1/2, 1/3 1/4, 1/5, 1/6, 1/11. This patient should be assigned 3 points: 1 point (1/1-1/4), 1 point (1/5-1/6), 1 point (1/11).

Query explanation: Patient 25 should have 5 total points;
Date range 2015-10-02 - 2015-10-05 should be assigned 1 point;
Date range 2015-11-08 - 2015-11-09 should be assigned 1 point;
The other dates do not have another date within 3 days, they should be assigned 1 point.

WITH CTE AS
    (
    SELECT 
    ROW_NUMBER() OVER (PARTITION BY OrganizationMrn ORDER BY [Date]) AS ROWNUMBER, *
    FROM #RC
    )
    SELECT *, 
    ISNULL(DATEDIFF(DY,(SELECT OTHER.[Date] FROM CTE OTHER WHERE OTHER.OrganizationMrn = CTE.OrganizationMrn AND OTHER.ROWNUMBER = CTE.ROWNUMBER - 1), CTE.[Date]),0) AS DaysFromLastVisit, 
    CASE WHEN ISNULL(DATEDIFF(DY,(SELECT OTHER.[Date] FROM CTE OTHER WHERE OTHER.OrganizationMrn = CTE.OrganizationMrn AND OTHER.ROWNUMBER = CTE.ROWNUMBER - 1), CTE.[Date]),0) > 3 THEN 1 END AS POINTS
    FROM CTE
    ORDER BY OrganizationMrn, [Date];

    ROWNUMBER   OrganizationMrn Date        DaysFromLastVisit   POINTS
    1           25              2015-10-02  0                   NULL
    2           25              2015-10-03  1                   NULL
    3           25              2015-10-05  2                   NULL
    4           25              2015-11-08  34                  1
    5           25              2015-11-09  1                   NULL
    6           25              2016-03-04  116                 1
    7           25              2016-05-04  61                  1
    8           25              2016-05-10  6                   1

This is how #RC is being populated:

SELECT I.OrganizationMrn, CAST(R.DTTM AS DATE) AS Date
INTO #RC
FROM Standard SD 
    INNER JOIN Iorg I ON I.Person = SD.Patient
    INNER JOIN Result R ON I.Person = R.Patient
WHERE 
    R.Entry = 'note'
AND R.DTTM >= DATEADD(M,-12,GETDATE()) 
AND OrganizationMrn = '25'
ORDER BY I.OrganizationMrn;

OrganizationMrn Date
25              2015-10-02
25              2015-10-03
25              2015-10-05
25              2015-11-08
25              2015-11-09
25              2016-03-04
25              2016-05-04
25              2016-05-10

How can I modify this CASE statement to only assign points to one of of the 3 dates? It currently is assigning points to each day, 10/2, 10/3, 10/5.

CASE 
WHEN ISNULL(DATEDIFF(DY,(SELECT OTHER.[Date] FROM CTE OTHER WHERE OTHER.OrganizationMrn = CTE.OrganizationMrn AND OTHER.ROWNUMBER = CTE.ROWNUMBER - 1), CTE.[Date]),0) <= 3 
THEN 1 ELSE 0 END AS POINTS

Can you provide input table structure and sample data for that table? — Kannan Kandasamy, Sep 12 '16 at 15:41
What you have here is a standard CTE with a windowing function, not a recursive CTE. You don't really need a recursive CTE if I'm understanding the problem correctly. You need a simple subquery you can join to validate your date restrictions — Steve Mangiameli, Sep 12 '16 at 16:24
I don't need a recursive CTE if it is not necessary. I thought the recursive CTE would be the best way to group the days then move to the next set. Any tips on how to group the rows witihin 3 days of each other then move onto the next set? — JBritton, Sep 12 '16 at 18:32
I've included my answer which should be simpler, faster and easier to understand :-) — Kamil Gosciminski, Sep 12 '16 at 20:25

score 1 · Accepted Answer · answered Sep 12 '16 at 18:55

Do you need to output each individual group or is it enough to know the point value? If the latter, you can think of this as a variation of the "gaps and islands" problem. There is an excellent article here if you want a deep dive. I'm adapting one of the code snippets from that page here.

Define a starting point as a record that has no records within 3 days prior to it. An end point is a record that has no records within 3 days after. Once each island has been identified, we can take the number of days between the starting and ending point, and determine how many 3 day groups fit within it by dividing and rounding the answer up. Note: Code below is hard-coded for organization 1.

CREATE TABLE #t(
    OrganizationMrn int,
    VisitDate date)

INSERT #t(OrganizationMrn, VisitDate) VALUES 
    (1, '1/1/2016'),
    (1, '1/2/2016'),
    (1, '1/3/2016'),
    (1, '1/4/2016'),
    (1, '1/5/2016'),
    (1, '1/6/2016'),
    (1, '1/11/2016')

;WITH StartingPoints AS (
    SELECT VisitDate, ROW_NUMBER() OVER (ORDER BY VisitDate) AS Sequence FROM #t AS A 
    WHERE A.OrganizationMrn = 1 AND NOT EXISTS (
        SELECT * FROM #t AS B 
        WHERE B.OrganizationMrn = A.OrganizationMrn AND 
            B.VisitDate >= DATEADD(day, -4, A.VisitDate)
            AND 
            B.VisitDate < A.VisitDate
        )
),
EndingPoints AS (
    SELECT VisitDate, ROW_NUMBER() OVER (ORDER BY VisitDate) AS Sequence FROM #t AS A 
    WHERE A.OrganizationMrn = 1 AND NOT EXISTS (
        SELECT * FROM #t AS B 
        WHERE B.OrganizationMrn = A.OrganizationMrn AND 
            B.VisitDate <= DATEADD(day, 4, A.VisitDate)
            AND 
            B.VisitDate > A.VisitDate
        )
)

SELECT 
     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)

Thanks dazedandconfused, I think its working! I've spent way too long trying to figure this out. — JBritton, Sep 12 '16 at 19:29

Kamil Gosciminski · Answer 2 · 2016-09-12T20:24:59.547

Straightforward logic getting first previous date into the current row using lag() window function and then based on the output assigning points or not with conditional sum().

Assign a point when previous date doesn't fall between date and 3 days behind or when there is no previos date to an event:

select 
  organizationmrn, 
  sum(case when 
        prev_date not between dateadd(day, -3, date) and date 
        or prev_date is null 
      then 1 else 0 end) as points
from (
  select 
    *, 
    lag(date,1) over (partition by organizationmrn order by date) as prev_date
  from rc 
  ) calculate_prev_date
group by organizationmrn

Result

 organizationmrn | points
-----------------+--------
              25 |      5

score 1 · Answer 3 · answered May 26 '21 at 09:48

I am also new to SQL land. I've found this article from https://bertwagner.com/posts/gaps-and-islands, explaining pretty well how to solve this "gaps & islands problem".

Here is his sample SQL that is working for me:

DROP TABLE IF EXISTS #OverlappingDateRanges;
CREATE TABLE #OverlappingDateRanges (StartDate date, EndDate date);

INSERT INTO #OverlappingDateRanges
SELECT '8/24/2017', '9/23/2017'  UNION ALL
SELECT '8/24/2017', '9/20/2017'  UNION ALL 
SELECT '9/23/2017', '9/27/2017'  UNION ALL 
SELECT '9/25/2017', '10/10/2017' UNION ALL
SELECT '10/17/2017','10/18/2017' UNION ALL 
SELECT '10/25/2017','11/3/2017'  UNION ALL 
SELECT '11/3/2017', '11/15/2017'


SELECT
    MIN(StartDate) AS IslandStartDate,
    MAX(EndDate) AS IslandEndDate
FROM (
    SELECT
        *,
        CASE WHEN Groups.PreviousEndDate >= StartDate THEN 0 ELSE 1 END AS IslandStartInd,
        SUM(CASE WHEN Groups.PreviousEndDate >= StartDate THEN 0 ELSE 1 END) OVER (ORDER BY Groups.RN) AS IslandId
    FROM (
        SELECT
            ROW_NUMBER() OVER(ORDER BY StartDate,EndDate) AS RN,
            StartDate,
            EndDate,
            LAG(EndDate,1) OVER (ORDER BY StartDate, EndDate) AS PreviousEndDate
        FROM
            #OverlappingDateRanges
    ) Groups
) Islands
GROUP BY
    IslandId
ORDER BY 
    IslandStartDate

A small adaption, that was necessary when there is a date, that coveres the complete time of another date (e.g. '9/20/2017', '10/27/2017' fully coveres '9/23/2017', '9/27/2017'): ```CASE WHEN LAG(EndDate,1) OVER (ORDER BY StartDate, EndDate) > LAG(EndDate,2, '0001-01-01') OVER (ORDER BY StartDate, EndDate) THEN LAG(EndDate,1) OVER (ORDER BY StartDate, EndDate) ELSE LAG(EndDate,2) OVER (ORDER BY StartDate, EndDate) END AS PreviousEndDate``` — LittleLynx, Mar 30 '22 at 06:55
Also I could use this code and adapt it to group islands by an ID by adding to the `OVER` clause: ``` OVER (PARTITION BY id ORDER BY id, StartDate, EndDate)``` — LittleLynx, Mar 30 '22 at 06:58

SQL: Recursive CTE by grouped date range

3 Answers3