Determine Contiguous Dates In Sql Gaps And Islands

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 wi

Solution 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, 1as inc from tt
      union all
      select patient, enddate as dte, -1as inc from tt
     ),
     dd as (
       select patient, dte, sum(sum(inc)) over (orderby dte) as cume_inc
       from d
       groupby patient, dte
      ),
     ddd as (
       select dd.*, sum(casewhen cume_inc = 0then1else0end) over (partition by patient orderby dte desc) as grp
       from dd
      )
select patient, min(dte) as startdate, max(dte) as enddate
from ddd
groupby grp;

Here is a SQL Fiddle.