SQL: Islands and Gaps

gistfile1.txt

drop table #overlappingDateRages

create table #overlappingDateRages(
  StartDate date,
  EndDate date
)

insert into #overlappingDateRages(StartDate, EndDate)
select '8/24/2017' StartDate , '9/23/2017' EndDate  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'


;with GroupsCte as 
(
select 
 ROW_NUMBER() over(order by StartDate, EndDate) as RN,
 StartDate,
 EndDate,
 LAG(EndDate,1) OVER (order by StartDate, EndDate) as PreviousEndDate
 from #overlappingDateRages
 ),
 IslandsCte as (
 select 
 *,
 case when g.PreviousEndDate >= StartDate then 0 else 1 end as IslandStartInd,
 sum (case when g.PreviousEndDate >= StartDate then 0 else 1 end) OVER (order by g.RN) as IslandId
 from GroupsCte g )

select 
  min(StartDate) as IslandStartDate,
  max(EndDate) as IslandEndDate
from
  IslandsCte
group by 
  IslandId
order by 
  IslandStartDate