common table expression - Determine contiguous dates in SQL gaps and islands

Question

I have a situation where a single patient can receive multiple services. These services can have overlapping dates, and can gaps and islands. I am trying to write a query that will show the contiguous length of time that the patient was receiving some kind of service.

Table is as follows:

CREATE TABLE #tt
(Patient    VARCHAR(10), StartDate DATETIME, EndDate DATETIME)
INSERT INTO #tt
VALUES
('Smith',   '2014-04-13',   '2014-06-04'),
('Smith',   '2014-05-07',   '2014-05-08'),
('Smith',   '2014-06-21',   '2014-09-19'),
('Smith',   '2014-08-27',   '2014-08-27'),
('Smith',   '2014-08-28',   '2014-09-19'),
('Smith',   '2014-10-30',   '2014-12-16'),
('Smith',   '2015-05-21',   '2015-07-03'),
('Smith',   '2015-05-22',   '2015-07-03'),
('Smith',   '2015-05-26',   '2015-11-30'),
('Smith',   '2015-06-25',   '2016-06-08'),
('Smith',   '2015-07-22',   '2015-10-22'),
('Smith',   '2016-08-11',   '2016-09-02'),
('Smith',   '2017-06-02',   '2050-01-01'),
('Smith',   '2017-12-22',   '2017-12-22'),
('Smith',   '2018-03-25',   '2018-06-30')

As you can see, many of the dates overlap. Ultimately what I want to see is the following results, which will show the dates where the patient was receiving at least one service, like so:

Patient     |StartDate        |EndDate
--------------------------------------
Smith       |2014-04-13       |2016-06-04
Smith       |2014-06-21       |2014-09-19
Smith       |2014-10-30       |2014-12-16
Smith       |2015-05-21       |2016-06-08
Smith       |2016-08-11       |2016-09-02
Smith       |2017-06-02       |2050-01-01

I've gotten bleary eyed from looking at the various gaps and islands SQL code. I've started out with this CTE, but obviously it isn't working, and if I wanted this, I could have simply used SELECT PHN, Min(StartDate), MAX(EndDate)

WITH HCC_PAT 
AS 
(
    SELECT DISTINCT
    PHN,
    StartDate,
    EndDate,
    MIN (StartDate) OVER (  PARTITION BY  PHN ORDER BY StartDate
                                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PreviousStartDate,
    MAX (EndDate) OVER (    PARTITION BY  PHN ORDER BY EndDate
                                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PreviousEndDate 

FROM    #tt)

SELECT  DISTINCT --hcc_Pat.HCCClientKey,
        hcc_pat.PHN,
        hcc_pat.StartDate,
        ISNULL (LEAD (PreviousEndDate) OVER (PARTITION BY PHN ORDER BY ENDDATE), 'January 1, 2050') AS EndDate
FROM    HCC_PAT
WHERE   PreviousEndDate > StartDate 
AND     (StartDate < PreviousStartDate OR PreviousStartDate IS NULL)

Any help at this point would be gratefully appreciated

Answer 1

One method spreads the dates out, with an indicator of whether the service is starting or ending. Then a cumulative sum of the indicator can be used to define the different groups -- the zero values in the cumulative sum are when a period ends.

The final step is aggregation:

with d as (
      select patient, startdate as dte, 1 as inc from tt
      union all
      select patient, enddate as dte, -1 as inc from tt
     ),
     dd as (
       select patient, dte, sum(sum(inc)) over (order by dte) as cume_inc
       from d
       group by patient, dte
      ),
     ddd as (
       select dd.*, sum(case when cume_inc = 0 then 1 else 0 end) over (partition by patient order by dte desc) as grp
       from dd
      )
select patient, min(dte) as startdate, max(dte) as enddate
from ddd
group by grp;

Here is a SQL Fiddle.