Home » SQL & PL/SQL » SQL & PL/SQL » Grouping by a Date Range (Oracle 10g)
icon5.gif  Grouping by a Date Range [message #316533] Fri, 25 April 2008 10:29 Go to next message
Seshagiri
Messages: 13
Registered: October 2007
Location: United Kingdom
Junior Member

I have data like this in some table

Date Units

01-JAN-08 10
02-JAN-08 05
03-JAN-08 10

05-JAN-08 10
06-JAN-08 05

08-JAN-08 05

10-JAN-08 10
11-JAN-08 05
12-JAN-08 10

.............. ..
.............. ..



.............. ..


I want to write a generalized query where the output is

01-JAN-08 to 03-JAN-08 25

05-JAN-08 to 06-JAN-08 15

08-JAN-08 to 08-JAN-08 05

10-JAN-08 to 12-JAN-08 25

All the consecutive dates are put into 1 range and the sum of units is obtained.

Regards,
Sesha
Re: Grouping by a Date Range [message #316536 is a reply to message #316533] Fri, 25 April 2008 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use LAG function to create groups.

Regards
Michel

[Updated on: Fri, 25 April 2008 10:37]

Report message to a moderator

Re: Grouping by a Date Range [message #316543 is a reply to message #316536] Fri, 25 April 2008 10:55 Go to previous messageGo to next message
Seshagiri
Messages: 13
Registered: October 2007
Location: United Kingdom
Junior Member

I tried it for a day but could not succeed. I gave it up.

Regards,
Sesha





Re: Grouping by a Date Range [message #316548 is a reply to message #316543] Fri, 25 April 2008 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried.
As alsways if you post a test case (create table and insert statements) you will get more answers.

Regards
Michel
Re: Grouping by a Date Range [message #316561 is a reply to message #316533] Fri, 25 April 2008 13:42 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: HOW TO CHANGE SQL QUERY TO GET SPECIFIC RESULT
Next Topic: csv to table
Goto Forum:
  


Current Time: Thu Dec 08 02:05:16 CST 2016

Total time taken to generate the page: 0.10972 seconds