How can I sum durations grouped by overlapping times in SQL Server

Question

I am trying to create a stored proc in SQL Server 2008.

I have a "Timings" Table (which could have thousands of records):

StaffID | MachineID | StartTime          | FinishTime
1       | 1         | 01/01/2018 12:00   | 01/01/18 14:30
2       | 1         | 01/01/2018 12:00   | 01/01/18 13:00
3       | 2         | 01/01/2018 12:00   | 01/01/18 13:00
3       | 2         | 01/01/2018 13:00   | 01/01/18 14:00
4       | 3         | 01/01/2018 12:00   | 01/01/18 12:30
5       | 3         | 01/01/2018 11:00   | 01/01/18 13:30

This shows how long each staff member was working on each machine.

I would like to produce a results table as below:

MachineID | StaffQty | TotalMins
1         | 1        | 90
1         | 2        | 60
2         | 1        | 120
3         | 1        | 120    
3         | 2        | 30    

This would show how many minutes each machine had only one person using it, how many minutes each machine had 2 people using it etc.

Normally, I would post what I have tried so far, but all my attempts seem to be so far away, I don't think there is much point. Obviously, I would be very grateful of a complete solution but I would also appreciate even just a little nudge in the right direction.

Answer 1

I think this answers your question:

declare @t table (StaffID int, MachineID int, StartTime datetime2,FinishTime datetime2)
insert into @t(StaffID,MachineID,StartTime,FinishTime) values
(1,1,'2018-01-01T12:00:00','2018-01-01T14:30:00'),
(2,1,'2018-01-01T12:00:00','2018-01-01T13:00:00'),
(3,2,'2018-01-01T12:00:00','2018-01-01T12:30:00')

;With Times as (
    select MachineID,StartTime as Time from @t
    union
    select MachineID,FinishTime from @t
), Ordered as (
    select
        *,
        ROW_NUMBER() OVER (PARTITION BY MachineID ORDER BY Time) rn
    from Times
), Periods as (
    select
        o1.MachineID,o1.Time as StartTime,o2.Time as FinishTime
    from
        Ordered o1
            inner join
        Ordered o2
            on
                o1.MachineID = o2.MachineID and
                o1.rn = o2.rn - 1
)
select
    p.MachineID,
    p.StartTime,
    MAX(p.FinishTime) as FinishTime,
    COUNT(*) as Cnt,
    DATEDIFF(minute,p.StartTime,MAX(p.FinishTime)) as TotalMinutes
from
    @t t
        inner join
    Periods p
        on
            p.MachineID = t.MachineID and
            p.StartTime < t.FinishTime and
            t.StartTime < p.FinishTime
group by p.MachineID,p.StartTime

Results:

MachineID   StartTime                   FinishTime                  Cnt         TotalMinutes
----------- --------------------------- --------------------------- ----------- ------------
1           2018-01-01 12:00:00.0000000 2018-01-01 13:00:00.0000000 2           60
1           2018-01-01 13:00:00.0000000 2018-01-01 14:30:00.0000000 1           90
2           2018-01-01 12:00:00.0000000 2018-01-01 12:30:00.0000000 1           30

Hopefully you can see what each of the CTEs is doing. The only place where this may not give you exactly the results you're seeking is if one person's FinishTime is precisely equal to another person's StartTime on the same machine. Should be rare in real data hopefully.

Answer 2

For Sql server 2012+,

Please mention your Sql server version.

Try my script with other sample data. Please post other sample data if it is not working.

I think my script can be fix for other Test scenario.

create table #temp(StaffID int,MachineID int,StartTime datetime,FinishTime datetime)

insert into #temp VALUES

(1, 1,'01/01/2018 12:00','01/01/18 14:30')

,(2, 1,'01/01/2018 12:00','01/01/18 13:00')

,(3, 2,'01/01/2018 12:00','01/01/18 12:30')

;

WITH CTE

AS (

SELECT t.*

,t1.StaffQty

,datediff(MINUTE, t.StartTime, t.FinishTime) TotalMinutes

FROM #temp t

CROSS APPLY (

SELECT count(*) StaffQty

FROM #temp t1

WHERE t.machineid = t1.machineid

AND (

t.StartTime >= t1.StartTime

AND t.FinishTime <= t1.FinishTime

)

) t1

)

SELECT MachineID

,StaffQty

,TotalMinutes - isnull(LAG(TotalMinutes, 1) OVER (

PARTITION BY t.MachineID ORDER BY t.StartTime

,t.FinishTime

), 0)

FROM cte t

　
drop table #temp

for Sql server 2008,

;

WITH CTE
AS (
SELECT t.*
,t1.StaffQty
,datediff(MINUTE, t.StartTime, t.FinishTime) TotalMinutes
,ROW_NUMBER() OVER (
PARTITION BY t.machineid ORDER BY t.StartTime
,t.FinishTime

) rn
FROM #temp t
CROSS APPLY (
SELECT count(*) StaffQty
FROM #temp t1
WHERE t.machineid = t1.machineid
AND (
t.StartTime >= t1.StartTime
AND t.FinishTime <= t1.FinishTime
)

) t1
)

SELECT t.MachineID
,t.StaffQty
,t.TotalMinutes - isnull(t1.TotalMinutes, 0) TotalMinutes
FROM cte t
OUTER APPLY (
SELECT TOP 1 TotalMinutes
FROM cte t1
WHERE t.MachineID = t1.machineid
AND t1.rn < t.rn
ORDER BY t1.rn DESC
) t1