CODEWITHSUNDEEP
sql-servert-sql
user949110
user949110

Reputation: 553

SQL Server : calculating total duration of overlapping timespans

I have a dataset which is essentially a set of 'group of tasks' with their start and end times, as follows

Group         Name              StartTime                  EndTime
---------------------------------------------------------------------
GroupA        Task1          01-01-2018T09:00        01-01-2018T11:00
GroupA        Task2          01-01-2018T10:00        01-01-2018T12:00
GroupA        Task3          01-01-2018T10:00        01-01-2018T13:00
GroupA        Task4          01-01-2018T20:00        01-01-2018T22:00

I need to calculate the total duration of the tasks in the group (or the group duration), taking into account the overlaps between them. For example, in the above example the total group duration should be 6 hours

How can I achieve this in T-SQL?

Upvotes: 1

Views: 203

Answers (2)

uzi
uzi

Reputation: 4146

Here's another option using self join

declare @t table ([Group] varchar(10), Name varchar(10), StartTime datetime, EndTime datetime)
insert into @t
values 
('GroupA', 'Task1', '20180101 09:00', '20180101 11:00')
, ('GroupA', 'Task2', '20180101 10:00', '20180101 12:00')
, ('GroupA', 'Task3', '20180101 10:00', '20180101 13:00')
, ('GroupA', 'Task4', '20180101 20:00', '20180101 22:00')

select
    [Group], StartTime = min(StartTime), EndTime
from (
    select
        a.[Group], a.StartTime, EndTime = max(b.EndTime)
    from 
        @t a
        left join @t b on a.[Group] = b.[Group] and a.StartTime < b.EndTime and a.EndTime > b.StartTime
    group by a.[Group], a.StartTime
) t
group by [Group], EndTime

Upvotes: 1

Squirrel
Squirrel

Reputation: 24813

Using Recursive CTE

; with 
tbl as  -- your sample data
(
    select [Group] = 'GroupA', Name = 'Task1', StartTime = '2018-01-01 09:00', EndTime = '2018-01-01 11:00' union all
    select [Group] = 'GroupA', Name = 'Task2', StartTime = '2018-01-01 10:00', EndTime = '2018-01-01 12:00' union all
    select [Group] = 'GroupA', Name = 'Task3', StartTime = '2018-01-01 10:00', EndTime = '2018-01-01 13:00' union all
    select [Group] = 'GroupA', Name = 'Task4', StartTime = '2018-01-01 20:00', EndTime = '2018-01-01 22:00'
),
cte as  -- cte to assign rn to each row
(
    select  *, rn = row_number() over(partition by [Group] order by StartTime, EndTime)
    from    tbl
),
rcte as -- recursive cte to find grp with start and end time
(
    select  [Group], StartTime, EndTime, rn, grp = 1
    from    cte
    where   rn  = 1

    union all

    select  r.[Group], 
        StartTime   = case  when    c.StartTime between r.StartTime and r.EndTime
                    then    r.StartTime
                    else    c.StartTime
                    end,
        EndTime     = c.EndTime,
        c.rn,
        grp     = case  when    c.StartTime between r.StartTime and r.EndTime
                    then    r.grp
                    else    r.grp + 1
                    end
    from    rcte r
        inner join cte c    on  r.[Group]   = c.[Group]
                    and r.rn        = c.rn - 1
)
select  *   -- the datediff() for StartTime and EndTime is what you want
from
(
    select  *, r = row_number() over (partition by grp order by rn desc)
    from    rcte
) d
where   d.r = 1

Upvotes: 0

Related Questions