Skip to Main Content

Tabibitosan method group data

PugzlyMay 28 2022

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);
Comments
Post Details
Added on May 28 2022
4 comments
45 views