I'm trying to use Tabibitosan method to group DATE, events but I can't seem to make it work because I'm getting a syntax error.
The output I want to generate is below.
start date end date event
05/20/2022 05/24/2022 on
05/25/2022 05/25/2022 idle
05/26/2022 05/27/2022 on
05/28/22 05/29/22 idle
05/30/22 05/31/22 off
06/01/22 06/02/22 idle
My test CASE. Thanks in advance to all who answer.
CREATE TABLE t02(dt, event) AS
SELECT DATE '2022-05-20', 'ON' FROM DUAL UNION ALL
SELECT DATE '2022-05-21', 'ON' FROM DUAL UNION ALL
SELECT DATE '2022-05-22', 'ON' FROM DUAL UNION ALL
SELECT DATE '2022-05-22', 'ON' FROM DUAL UNION ALL
SELECT DATE '2022-05-23', 'ON' FROM DUAL UNION ALL
SELECT DATE '2022-05-24', 'ON' FROM DUAL UNION ALL
SELECT DATE '2022-05-25', 'IDLE' FROM DUAL UNION ALL
SELECT DATE '2022-05-26', 'ON' FROM DUAL UNION ALL
SELECT DATE '2022-05-27', 'ON' FROM DUAL UNION ALL
SELECT DATE '2022-05-28', 'IDLE' FROM DUAL UNION ALL
SELECT DATE '2022-05-29', 'IDLE' FROM DUAL UNION ALL
SELECT DATE '2022-05-30', 'OFF' FROM DUAL UNION ALL
SELECT DATE '2022-05-31', 'OFF' FROM DUAL UNION ALL
SELECT DATE '2022-06-01', 'IDLE' FROM DUAL UNION ALL
SELECT DATE '2022-06-02', 'IDLE' FROM DUAL;
select min(dt),max(dt),event,count(*)
from (select dt,
dt - Row_Number() over(order by dt) as grp
from t02)
group by grp,event
order by min(dt);