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

**编辑：我们当前的服务器是 SQL 2008 R2，因此 LAG/LEAD 功能将不起作用。

我正在尝试获取表中的多个数据流并将它们组合成 1 个数据流。鉴于下面的 3 个数据流，我希望最终结果是 1 个优先考虑状态“on”的流。递归似乎是最好的选择，但到目前为止我还没有运气组合一个查询来完成我想要的任务。

CREATE TABLE #Dates(
    id INT IDENTITY,
    status VARCHAR(4),
    StartDate Datetime,
    EndDate Datetime,
    booth int)

INSERT #Dates
VALUES
( 'off','2015-01-01 08:00','2015-01-01 08:15',1),
( 'on','2015-01-01 08:15','2015-01-01 09:15',1),
( 'off','2015-01-01 08:50','2015-01-01 09:00',2),
( 'on','2015-01-01 09:00','2015-01-01 09:30',2),
( 'off','2015-01-01 09:30','2015-01-01 09:35',2),
( 'on','2015-01-01 09:35','2015-01-01 10:15',2),
( 'off','2015-01-01 09:30','2015-01-01 10:30',3),
( 'on','2015-01-01 10:30','2015-01-01 11:00',3)

status  StartDate   EndDate
---------------------------
off     08:00       08:15
on      08:15       09:15
off     08:50       09:00
on      09:00       09:30
off     09:30       09:35
on      09:35       10:15
off     09:30       10:30
on      10:30       11:00

最终结果：

status  StartDate   EndDate
---------------------------
off     8:00        8:15
on      8:15        9:15
on      9:15        9:30
off     9:30        9:35
on      9:35        10:15
off     10:15       10:30
on      10:30       11:00

本质上，只要存在“开启”状态，它就应该覆盖任何并发的“关闭”状态。

Source:
|----off----||---------on---------|
                     |---off--||------on----||---off---||--------on------|
                                             |--------------off------------------||------on------|

Result (Either result would work):
|----off----||----------on--------||---on---||---off---||--------on------||-off--||------on------|
|----off----||----------on------------------||---off---||--------on------||-off--||------on------|

这是我能找到的 2008 年最简单的版本：

; with Data (Date) as (
select StartDate from Dates
union
select EndDate from Dates),

Ranges (StartDate, Status) as (
select D.Date, D2.Status
from Data D
outer 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,
Status
from Ranges R
order by R.StartDate

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

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

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