SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE actvt ( empl_number int NOT NULL, actvt_starttime datetime NOT NULL, actvt_endtime datetime NOT NULL, actvt_id int NOT NULL ); INSERT actvt VALUES (1,'2022-07-01 15:00:00', '2022-07-01 15:15:00',62), (1,'2022-07-01 15:15:00', '2022-07-01 15:30:00',62), (1, '2022-07-01 15:30:00', '2022-07-01 15:45:00',62), (1, '2022-07-01 15:45:00', '2022-07-01 16:00:00',62), (1, '2022-07-01 16:00:00', '2022-07-01 16:15:00',62), (1, '2022-07-01 16:15:00', '2022-07-01 16:30:00',62), (2, '2022-07-01 16:30:00', '2022-07-01 16:45:00',62), (2, '2022-07-01 16:45:00', '2022-07-01 17:00:00',62), (1, '2022-07-01 17:00:00', '2022-07-01 17:15:00',29), (1, '2022-07-01 17:15:00', '2022-07-01 17:30:00',29), (2, '2022-07-01 17:30:00', '2022-07-01 17:45:00',29), (2, '2022-07-01 17:45:00', '2022-07-01 18:00:00',29), (3, '2022-07-01 18:00:00', '2022-07-01 18:15:00',62), (3, '2022-07-01 18:15:00', '2022-07-01 18:30:00',62), (3, '2022-07-01 18:30:00', '2022-07-01 18:45:00',62), (3, '2022-07-01 18:45:00', '2022-07-01 19:00:00',62), (3, '2022-07-01 19:00:00', '2022-07-01 19:15:00',62), (4, '2022-07-01 19:15:00', '2022-07-01 19:30:00',62), (4, '2022-07-01 19:30:00', '2022-07-01 19:45:00',62), (4, '2022-07-01 19:45:00', '2022-07-01 20:00:00',62), (4, '2022-07-01 20:00:00', '2022-07-01 20:15:00',9), (4, '2022-07-01 20:15:00', '2022-07-01 20:30:00',9), (1, '2022-07-01 20:30:00', '2022-07-01 20:45:00',9), (1, '2022-07-01 20:45:00', '2022-07-01 21:00:00',9), (2, '2022-07-01 21:00:00', '2022-07-01 21:15:00',9), (2, '2022-07-01 21:15:00', '2022-07-01 21:30:00',9), (3, '2022-07-01 21:30:00', '2022-07-01 21:45:00',9), (3, '2022-07-01 21:45:00', '2022-07-01 22:00:00',9); With MX AS ( SELECT empl_number, actvt_id, actvt_starttime, actvt_endtime , MAX(actvt_endtime) OVER (PARTITION BY empl_number, actvt_id ORDER BY actvt_starttime, actvt_endtime ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as Endstop FROM actvt), Ch as (SELECT empl_number, actvt_id, actvt_starttime, actvt_endtime, EndStop, CASE WHEN Endstop Is Null or Endstop < actvt_starttime Then 1 ELSE 0 end as endstop_stat , SUM(CASE WHEN Endstop Is Null or Endstop < actvt_starttime Then 1 ELSE 0 END) OVER (PARTITION BY empl_number, actvt_id ORDER BY actvt_starttime, Endstop) as chunk FROM MX ) SELECT empl_number, actvt_id , MIN(actvt_starttime) actvt_starttime , MAX(actvt_endtime) actvt_endtime FROM Ch GROUP BY empl_number, actvt_id, Chunk ORDER BY empl_number, actvt_id, Chunk