Calculate the sum of minutes in overlapping timeranges

Question

I need to calculate the sum of minutes of terms. Overlapping terms should not count multiple times.

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

The result from the data above should be 180 minutes (3 hours). What is the easiest and fastest way to to this?

Answer 1

With the help of a cross apply.

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

Returns

Minutes
180

The sub-query returns

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