Home » SQL & PL/SQL » SQL & PL/SQL » Grouping by a Date Range (Oracle 10g)
|
|
|
|
Re: Grouping by a Date Range [message #316561 is a reply to message #316533] |
Fri, 25 April 2008 13:42   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
This is one way of doing it. I will give you partial solution. Try to figure out how to do the grouping.
1 with
2 t
3 as
4 (
5 select to_date('01-JAN-08','dd-mon-yy') date_val, 10 num from dual union all
6 select to_date('02-JAN-08','dd-mon-yy'), 05 from dual union all
7 select to_date('03-JAN-08','dd-mon-yy'), 10 from dual union all
8 select to_date('05-JAN-08','dd-mon-yy'), 10 from dual union all
9 select to_date('06-JAN-08','dd-mon-yy'), 05 from dual union all
10 select to_date('08-JAN-08','dd-mon-yy'), 05 from dual union all
11 select to_date('10-JAN-08','dd-mon-yy'), 10 from dual union all
12 select to_date('11-JAN-08','dd-mon-yy'), 05 from dual union all
13 select to_date('12-JAN-08','dd-mon-yy'), 10 from dual
14 ),
15 t1
16 as
17 (
18 select date_val, num,
19 case when trunc(date_val) - nvl(lag(trunc(date_val)) over(order by date_val),trunc(date_val)-2) > 1
20 then rownum
21 else null
22 end rn from t
23 ),
24 t2
25 as
26 (
27 .....
28 )
29 select min(date_val), max(date_val), sum(num) from t2
30* group by grp
SQL> /
MIN(DATE_VAL) MAX(DATE_VAL) SUM(NUM)
------------------ ------------------ ----------
01-JAN-08 03-JAN-08 25
05-JAN-08 06-JAN-08 15
08-JAN-08 08-JAN-08 5
10-JAN-08 12-JAN-08 25
Regards
Raj
|
|
|
Re: Grouping by a Date Range [message #317106 is a reply to message #316533] |
Tue, 29 April 2008 09:40  |
Seshagiri
Messages: 13 Registered: October 2007 Location: United Kingdom
|
Junior Member |

|
|
Hi Raj,
Thank you for the hint. Now the complete query is written as
with
t
as
(
select to_date('01-JAN-08','dd-mon-yy') date_val, 10 num from dual union all
select to_date('02-JAN-08','dd-mon-yy'), 05 from dual union all
select to_date('03-JAN-08','dd-mon-yy'), 10 from dual union all
select to_date('05-JAN-08','dd-mon-yy'), 10 from dual union all
select to_date('06-JAN-08','dd-mon-yy'), 05 from dual union all
select to_date('08-JAN-08','dd-mon-yy'), 05 from dual union all
select to_date('10-JAN-08','dd-mon-yy'), 10 from dual union all
select to_date('11-JAN-08','dd-mon-yy'), 05 from dual union all
select to_date('12-JAN-08','dd-mon-yy'), 10 from dual
),
t1
as
(
select date_val, num,
case when trunc(date_val) - nvl(lag(trunc(date_val)) over(order by date_val),trunc(date_val)-2) > 1
then rownum
else null
end rn from t
),
t2
as
(
select date_val, num, nvl(rn, max(rn) over(order by date_val range unbounded preceding)) grp from t1
)
select min(date_val), max(date_val), sum(num) from t2
group by grp
Regards,
Sesha
|
|
|
Goto Forum:
Current Time: Sat Feb 15 14:21:28 CST 2025
|