sql - 计算重叠时间范围内的分钟数总和

Question

我需要计算术语的分钟总和。重叠的术语不应多次计算。

Declare @Terms table (Start DATETIME, Finish DATETIME)
INSERT INTO @Terms values
    ('2016-8-3 08:00','2016-8-3 09:00'),
    ('2016-8-3 09:00','2016-8-3 10:00'),
    ('2016-8-3 08:00','2016-8-3 09:30'),   -- overlapping term
    ('2016-8-3 11:00','2016-8-3 12:00')

以上数据的结果应该是 180 分钟(3 小时)。最简单和最快的方法是什么？

Answer 1

在交叉申请的帮助下。

Declare @Terms table (Start DATETIME, Finish DATETIME)
INSERT INTO @Terms values
    ('2016-8-3 08:00','2016-8-3 09:00'),
    ('2016-8-3 09:00','2016-8-3 10:00'),
    ('2016-8-3 08:00','2016-8-3 09:30'),   -- overlapping term
    ('2016-8-3 11:00','2016-8-3 12:00')

Select Minutes = sum(Minutes)
 From (
        Select Distinct 
               B.DateR1
              ,B.DateR2
              ,Minutes = DateDiff(Minute,B.DateR1,B.DateR2)
         From @Terms A
         Cross Apply (
                      Select DateR1=Min(Start)
                            ,DateR2=max(Finish) 
                       From  @Terms 
                       Where Start <= A.Finish and Finish >= A.Start
                     ) B
 ) A

退货

Minutes
180

子查询返回

DateR1                     DateR2                   Minutes
2016-08-03 08:00:00.000    2016-08-03 10:00:00.000  120
2016-08-03 11:00:00.000    2016-08-03 12:00:00.000  60