如何在SQL Server中合并时间间隔

Question

假设我有以下内容的事件表personId，startDate和endDate。

我想知道X人花了多少时间做一个事件（事件可以互相覆盖）。

如果此人只有1个事件，则很简单： datediff(dd, startDate, endDate)

如果此人有2个事件，那将很棘手。

我将为预期的结果设置一些方案。

场景1

startDate endDate
1         4
3         5

这意味着他的结果应该是从1到5的datediff

方案2

startDate endDate
1         3
6         9

这意味着他的结果应该是一些datediff(dd,1,3)和datediff(dd,6,9)

如何在sql查询中获得此结果？我只能想到一堆if语句，但是同一个人可以有n个事件，因此查询确实会造成混乱。

碎纸机编辑： 我想添加第三个方案：

startDate endDate
1       5
4       8
11      15

碎纸机方案所需的结果：

（1,5）和（4,8）在（1,8）中合并，因为它们重叠，那么我们需要datediff(1,8) + datediff(11,15)=> 7 + 4 =>
11

Answer 1

您可以使用递归CTE构建日期列表，然后计算不同的日期。

declare @T table
(
  startDate date,
  endDate date
);

insert into @T values
('2011-01-01', '2011-01-05'),
('2011-01-04', '2011-01-08'),
('2011-01-11', '2011-01-15');

with C as
(
  select startDate,
         endDate
  from @T
  union all
  select dateadd(day, 1, startDate),
         endDate
  from C
  where dateadd(day, 1, startDate) < endDate       
)
select count(distinct startDate) as DayCount
from C
option (MAXRECURSION 0)

结果：

DayCount
-----------
11

或者，您可以使用数字表。在这里我使用master..spt_values：

declare @MinStartDate date
select @MinStartDate = min(startDate)
from @T

select count(distinct N.number)
from @T as T
  inner join master..spt_values as N
    on dateadd(day, N.Number, @MinStartDate) between T.startDate and dateadd(day, -1, T.endDate)
where N.type = 'P'