How to use recursive ctes to see if there are any contiguous date range and combine those date ranges into a single one?

Question

I am trying to look at my database of administered table where we can see when a member has been administered and released from the rehab center. The sample table would look something like this:

SAMPLE TABLE:

ID.	MEMID	START	END
01	99988	01/01/2020	03/30/2020
02	99988	04/01/2020	12/31/2020
03	99988	01/01/2021	12/31/2021
04	99988	04/01/2022	12/31/2022
05	99989	01/01/2015	12/31/2020
06	99990	01/01/2017	09/31/2017
07	99990	08/01/2017	12/31/2018
08	99991	01/01/2013	012/31/2017
09	99992	01/01/2017	09/31/2019
10	99992	10/01/2019	12/31/2021

EXPECTED OUTPUT:

MEMID	START	END
99988	01/01/2020	12/31/2021
99990	01/01/2017	12/31/2018
99992	01/01/2017	12/31/2021

I am supposed to get the patients who had contiguous administration i.e. if they leave on join on 5th july of 2015, leave on 3rd of october and come back on 4th october and leave on 7th of december, I'd need to have that patient in the output with a single date range i.e 5th of july to 7th of december. My query:

  SELECT id, mimed, start, end
  FROM administered

  UNION ALL

   (
    SELECT n.id, n.memid, n.start, n.end
        FROM administered n JOIN result r on n.memid = r.memid
        WHERE n.start = DATEADD(day, 1, r.end)
    
    )

    )

    SELECT MIN(start) as start, MAX(end) as end, memid
    FROM result
    GROUP BY memid
    OPTION(MAXRECURSION 0)

I'm pretty sure there's something that I am not understanding regarding how to set up a base case or even the termination condition on this one.

Answer 1

I think your sample data is not quite what you intend.

• September only has 30 days so the END value of "09/31/2017" for ID 06 will not work
• Even if the END date for ID 06 is fixed it overlaps ID 07 and is not contiguous as I understand it.
• March has 31 days so it seems like the END date for ID 01 should be "03/31/2020".

Provided those items are address this query will yield the desired result...

SELECT
      a4.MEMID
    , CONVERT (DATE, a4.First_START) AS [START]
    , CONVERT (DATE, a4.[END])       AS [END]
FROM  (
          SELECT
               a3.MEMID
             , a3.[START]
             , a3.[END]
             , a3.ID_group
             , FIRST_VALUE (a3.[START]) OVER (PARTITION BY a3.ID_group ORDER BY a3.[START]) AS [First_START]
             , ROW_NUMBER () OVER (PARTITION BY a3.ID_group ORDER BY a3.[START] DESC)       AS [Row_number]
             , COUNT (*) OVER (PARTITION BY a3.ID_group)                                    AS [Count]
          FROM (
                   SELECT
                        a2.MEMID
                      , a2.[START]
                      , a2.[END]
                      , a2.nextday
                      , a2.New_group
                      , SUM (a2.New_group) OVER (ORDER BY a2.MEMID, a2.[START]) AS [ID_group]
                   FROM (
                            SELECT
                                 a1.MEMID
                               , a1.[START]
                               , a1.[END]
                               , DATEADD (DAY, 1, a1.Previous_END) AS [nextday]
                               , IIF((MEMID <> a1.Previous_MEMID)
                                  OR (
                                         MEMID = a1.Previous_MEMID
                                     AND DATEADD (DAY, 1, a1.Previous_END) <> a1.[START]) -- rule out consecutive day situations
                                   , 1
                                   , 0)                            AS [New_group]
                            FROM (
                                     SELECT
                                          MEMID
                                        , [START]
                                        , [END]
                                        , LAG (MEMID) OVER (ORDER BY MEMID)                      AS [Previous_MEMID]
                                        , LAG ([END]) OVER (PARTITION BY MEMID ORDER BY [START]) AS [Previous_END]
                                     FROM MyTable
                                 ) a1
                        ) a2
               ) a3
      ) a4
WHERE a4.[Row_number] = 1
  AND a4.Count        > 1;

The basic approach is to establish groups of contiguous date ranges and then within each range grab first START date with the FIRST_VALUE() function and last END date with the ROW_NUMBER() function taking first row when sorted in descending order. It seemed to me that I should have been able to use the LAST_VALUE() function, but I could not get that to work. And finally, the non-contiguous rows (e.g. ID 04) are eliminated with the Count > 1 in the where clause at the end.

Here is the dbfiddle.