By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab
([id] int, [fk_machine] int, [status] int, [created_at] datetime)
;
INSERT INTO tab
([id], [fk_machine], [status], [created_at])
VALUES
(1, 2222, 0, '2020-08-19 22:15:03'),
(2, 2222, 3, '2020-08-19 22:15:30'),
(3, 2222, 5, '2020-08-19 23:07:20'),
(4, 2222, 1, '2020-08-20 00:20:23'),
(5, 2222, 0, '2020-08-20 00:45:12'),
(6, 2222, 5, '2020-08-20 01:20:00')
;
6 rows affected
with cte as
(
select fk_machine
,status
,start_minute
,end_time
,case
when end_time > dateadd(minute, 1,start_minute)
then datediff(s, created_at, dateadd(minute, 1,start_minute))
else datediff(s, created_at, end_time )
end as seconds
from
(
select fk_machine
,status
,created_at
,dateadd(minute, datediff(minute, 0, created_at), 0) as start_minute
,lead(created_at) over (order by created_at) as end_time
from tab
) as dt
union all
select fk_machine
,status
,dateadd(minute, 1,start_minute)
,end_time
,case
when end_time >= dateadd(minute, 2,start_minute)
then 60
else datediff(s, dateadd(minute, 1,start_minute), end_time)
end
from cte
where end_time > dateadd(minute, 1,start_minute)
)
select * from cte
| fk_machine | status | start_minute | end_time | seconds |
|---|---|---|---|---|
| 2222 | 0 | 2020-08-19 22:15:00.000 | 2020-08-19 22:15:30.000 | 27 |
| 2222 | 3 | 2020-08-19 22:15:00.000 | 2020-08-19 23:07:20.000 | 30 |
| 2222 | 3 | 2020-08-19 22:16:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:17:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:18:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:19:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:20:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:21:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:22:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:23:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:24:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:25:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:26:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:27:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:28:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:29:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:30:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:31:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:32:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:33:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:34:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:35:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:36:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:37:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:38:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:39:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:40:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:41:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:42:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:43:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:44:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:45:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:46:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:47:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:48:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:49:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:50:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:51:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:52:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:53:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:54:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:55:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:56:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:57:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:58:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 22:59:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 23:00:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 23:01:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 23:02:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 23:03:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 23:04:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 23:05:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 23:06:00.000 | 2020-08-19 23:07:20.000 | 60 |
| 2222 | 3 | 2020-08-19 23:07:00.000 | 2020-08-19 23:07:20.000 | 20 |
| 2222 | 5 | 2020-08-19 23:07:00.000 | 2020-08-20 00:20:23.000 | 40 |
| 2222 | 5 | 2020-08-19 23:08:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:09:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:10:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:11:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:12:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:13:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:14:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:15:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:16:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:17:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:18:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:19:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:20:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:21:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:22:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:23:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:24:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:25:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:26:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:27:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:28:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:29:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:30:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:31:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:32:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:33:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:34:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:35:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:36:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:37:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:38:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:39:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:40:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:41:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:42:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:43:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:44:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:45:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:46:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:47:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:48:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:49:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:50:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:51:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:52:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:53:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:54:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:55:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:56:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:57:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:58:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-19 23:59:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:00:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:01:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:02:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:03:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:04:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:05:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:06:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:07:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:08:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:09:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:10:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:11:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:12:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:13:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:14:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:15:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:16:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:17:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:18:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:19:00.000 | 2020-08-20 00:20:23.000 | 60 |
| 2222 | 5 | 2020-08-20 00:20:00.000 | 2020-08-20 00:20:23.000 | 23 |
| 2222 | 1 | 2020-08-20 00:20:00.000 | 2020-08-20 00:45:12.000 | 37 |
| 2222 | 1 | 2020-08-20 00:21:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:22:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:23:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:24:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:25:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:26:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:27:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:28:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:29:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:30:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:31:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:32:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:33:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:34:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:35:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:36:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:37:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:38:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:39:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:40:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:41:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:42:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:43:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:44:00.000 | 2020-08-20 00:45:12.000 | 60 |
| 2222 | 1 | 2020-08-20 00:45:00.000 | 2020-08-20 00:45:12.000 | 12 |
| 2222 | 0 | 2020-08-20 00:45:00.000 | 2020-08-20 01:20:00.000 | 48 |
| 2222 | 0 | 2020-08-20 00:46:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:47:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:48:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:49:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:50:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:51:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:52:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:53:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:54:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:55:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:56:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:57:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:58:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 00:59:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:00:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:01:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:02:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:03:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:04:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:05:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:06:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:07:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:08:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:09:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:10:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:11:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:12:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:13:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:14:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:15:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:16:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:17:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:18:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 0 | 2020-08-20 01:19:00.000 | 2020-08-20 01:20:00.000 | 60 |
| 2222 | 5 | 2020-08-20 01:20:00.000 | null | null |