By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table #Data (DTID integer, machineID integer, startTime datetime, endTime datetime, duration float)
insert into #Data (DTID, machineID, startTime, endTime, duration)
values
(1,333,'2023-09-21 11:00:00','2023-09-21 11:15:00',15.0),
(2,334,'2023-09-21 11:05:00','2023-09-21 11:10:00',5.0),
(3,333,'2023-09-21 11:17:00','2023-09-21 11:18:00',1.0),
(4,334,'2023-09-21 11:16:00','2023-09-21 11:20:00',4.0);
select *
from #Data;
| DTID | machineID | startTime | endTime | duration |
|---|---|---|---|---|
| 1 | 333 | 2023-09-21 11:00:00.000 | 2023-09-21 11:15:00.000 | 15 |
| 2 | 334 | 2023-09-21 11:05:00.000 | 2023-09-21 11:10:00.000 | 5 |
| 3 | 333 | 2023-09-21 11:17:00.000 | 2023-09-21 11:18:00.000 | 1 |
| 4 | 334 | 2023-09-21 11:16:00.000 | 2023-09-21 11:20:00.000 | 4 |
with
cte1 as
(
select d.DTID, d.machineID, a.flag, a.dt,
cnt = sum(flag) over (order by dt, machineID)
from #Data d
cross apply
(
values
(+1, d.startTime),
(-1, d.endTime)
) a (flag, dt)
),
cte2 as
(
select *,
startTime = dt,
endTime = case when cnt <> 0
then lead(dt) over (order by dt, flag)
end
from cte1
)
select DTID,
machineID = case when cnt = 2
then 999
when flag = -1
then case when c.machineID= 333 then 334 else 333 end
else machineID end,
startTime, endTime,
duration = datediff(minute, startTime, endTime)
* case when cnt = 1 then 0.5 else 1 end
from cte2 c
where endTime is not null
order by dt, machineID
| DTID | machineID | startTime | endTime | duration |
|---|---|---|---|---|
| 1 | 333 | 2023-09-21 11:00:00.000 | 2023-09-21 11:05:00.000 | 2.5 |
| 2 | 999 | 2023-09-21 11:05:00.000 | 2023-09-21 11:10:00.000 | 5.0 |
| 2 | 333 | 2023-09-21 11:10:00.000 | 2023-09-21 11:15:00.000 | 2.5 |
| 4 | 334 | 2023-09-21 11:16:00.000 | 2023-09-21 11:17:00.000 | 0.5 |
| 3 | 999 | 2023-09-21 11:17:00.000 | 2023-09-21 11:18:00.000 | 1.0 |
| 3 | 334 | 2023-09-21 11:18:00.000 | 2023-09-21 11:20:00.000 | 1.0 |
create table #Expected (DTID integer, machineID integer, startTime datetime, endTime datetime, duration float)
insert into #Expected (DTID, machineID, startTime, endTime, duration)
values
(1,333,'2023-09-21 11:00:00','2023-09-21 11:05:00',2.5),
(2,999,'2023-09-21 11:05:00','2023-09-21 11:10:00',5.0),
(3,333,'2023-09-21 11:10:00','2023-09-21 11:15:00',2.5),
(4,334,'2023-09-21 11:16:00','2023-09-21 11:17:00',0.5),
(5,999,'2023-09-21 11:17:00','2023-09-21 11:18:00',1.0),
(5,334,'2023-09-21 11:18:00','2023-09-21 11:20:00',1.0);
select *
from #Expected
| DTID | machineID | startTime | endTime | duration |
|---|---|---|---|---|
| 1 | 333 | 2023-09-21 11:00:00.000 | 2023-09-21 11:05:00.000 | 2.5 |
| 2 | 999 | 2023-09-21 11:05:00.000 | 2023-09-21 11:10:00.000 | 5 |
| 3 | 333 | 2023-09-21 11:10:00.000 | 2023-09-21 11:15:00.000 | 2.5 |
| 4 | 334 | 2023-09-21 11:16:00.000 | 2023-09-21 11:17:00.000 | 0.5 |
| 5 | 999 | 2023-09-21 11:17:00.000 | 2023-09-21 11:18:00.000 | 1 |
| 5 | 334 | 2023-09-21 11:18:00.000 | 2023-09-21 11:20:00.000 | 1 |