By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @LaborTickets TABLE (
TicketID INT IDENTITY(1,1),
StartTime DATETIME,
EndTime DATETIME
);
INSERT INTO @LaborTickets (StartTime, EndTime)
VALUES
('2025-02-04 06:00:00', '2025-02-04 07:00:00'),
('2025-02-04 05:30:00', '2025-02-04 07:30:00'),
('2025-02-04 05:00:00', '2025-02-04 07:00:00');
with
t as (select * from @LaborTickets),
-- List all "cuts", where a ticket starts or another stops:
-- they will be the only places where our "count of parallel tickets" (CTP) can change.
-- union (without all) ensures we have exactly one occurrence of each timestamp.
cuts as (select StartTime t from t union select EndTime from t),
-- Spans: between two cuts are the monotonic spans (the ranges where the CTP is stable).
ordCuts as
(
select
row_number() over (order by t) id,
t StartTime,
lead(t) over (order by t) EndTime,
datediff(s, t, lead(t) over (order by t)) / 3600.0 duration
from cuts
),
spans as (select * from ordCuts where EndTime is not null),
-- Tickets <-> spans link table: which individual spans is each ticket the union of?
ts as
(
select t.TicketID tid, s.id sid
from t, spans s
where t.StartTime <= s.StartTime and t.EndTime >= s.EndTime
),
| StartTime | EndTime | Hours |
|---|---|---|
| 2025-02-04 06:00:00.000 | 2025-02-04 07:00:00.000 | 0.33333333333333333 |
| 2025-02-04 05:30:00.000 | 2025-02-04 07:30:00.000 | 1.08333333333333333 |
| 2025-02-04 05:00:00.000 | 2025-02-04 07:00:00.000 | 1.08333333333333333 |