在SQL中合并具有重叠日期范围的记录

这是我能够弄清楚的2008年最简单的版本：

; with Data (Date) as (select StartDate from Datesunionselect EndDate from Dates),Ranges (StartDate, Status) as (select D.Date, D2.Statusfrom Data Douter apply (  select top 1 D2.Status  from Dates D2  where D2.StartDate <= D.Date and D2.EndDate > D.Date  order by case when Status = 'on' then 1 else 2 end) D2)select R.StartDate,(select min(D.Date) from Data D where D.Date > R.StartDate) as EndDate,Statusfrom Ranges Rorder by R.StartDate

即使状态与以前相同，它也会从每个起点/终点开始返回新行。没有找到任何简单的方法来组合它们。

编辑：将第一个CTE更改为此将合并行：

; with Data (Date) as (select distinct StartDate from Dates D1where not exists (Select 1 from Dates D2     where D2.StartDate < D1.StartDate and D2.EndDate > D1.StartDate and    Status = 'on')unionselect distinct EndDate from Dates D1where not exists (Select 1 from Dates D2     where D2.StartDate < D1.EndDate and D2.EndDate > D1.EndDate and    Status = 'on')),