Sunday, February 12, 2006

Group by continuous date range

SQL> select * from sales order by 1;

EVENT_TIME AMT
-------------------- ----------
06-01-18 62
06-01-19 43
06-01-20 18
06-01-22 5
06-01-23 94
06-01-24 34
06-01-25 88
06-01-26 57
06-01-27 5
06-01-30 72
06-01-31 96
06-02-01 5
06-02-04 43

SQL> with x as (
2 select * from sales order by 1 asc
3 )
4 select min(event_time) Start_date, max(event_time) End_date, sum(amt) Amount
5 from x
6 group by event_time - rownum
7 order by 1
8 /

START_DA END_DATE AMOUNT
-------- -------- ----------
06-01-18 06-01-20 123
06-01-22 06-01-27 283
06-01-30 06-02-01 173
06-02-04 06-02-04 43

No comments: