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

小编典典

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

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------|

阅读 196

2021-04-22

共1个答案

小编典典

这是我能够弄清楚的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')
),

2021-04-22