如何在 SQL Server 中合并时间间隔 [英] How to merge time intervals in SQL Server

问题描述

假设我有以下事件表，其中包含 personId、startDate 和 endDate.

Suppose I have the following an event table with personId, startDate and endDate.

我想知道 X 人在一个事件上花费了多少时间(这些事件可以相互覆盖).

I want to know how much time the person X spent doing an event (the events can override each other).

如果这个人只有 1 个事件，那很简单:datediff(dd, startDate, endDate)

If the person just has 1 event, its easy: datediff(dd, startDate, endDate)

如果这个人有 2 个事件，它就会变得棘手.

If the person has 2 events it gets tricky.

我会为预期的结果设置一些场景.

I'll set some scenarios for the expected results.

场景一

startDate endDate
1         4
3         5

这意味着他的结果应该是从 1 到 5 的日期差异

This means he the results should be the datediff from 1 to 5

场景 2

startDate endDate
1         3
6         9

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

this means he the results should be the some of datediff(dd,1,3) and datediff(dd,6,9)

如何在 sql 查询中获得此结果?我只能想到一堆if语句，但是同一个人可以有n个事件，所以查询会很混乱.

How can I get this result on an sql query? I can only think of a bunch of if statements, but the same person can have n events so the query will be really confusing.

Shredder Edit:我想添加第三个场景:

startDate endDate
1       5
4       8
11      15

粉碎机场景的预期结果:

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

(1,5) and (4,8) merge in (1,8) since they overlap then we need to datediff(1,8) + datediff(11,15) => 7 + 4 => 11

推荐答案

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

You can use a recursive CTE to build a list of dates and then count the distinct dates.

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:

Or you can use a numbers table. Here I use 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'    

这篇关于如何在 SQL Server 中合并时间间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！