Thread: Group by date range in plsql

I have table tab1 with 3 date fields date_1 , date_2 and date_3.
table tab1 can contain 6 months of data.

My requirement is I need difference b/w date_1 and date_2
based on the date_3 range (weekly)

Output is :

row 1 : week1 (number of records having difference 1 ) (number of records having difference 2) ........

row 2 : week2 (number of records having difference 1 ) (number of records having difference 2) ........
..
..
number of rows = number of weeks


Week Range <1day <2days <3days <4days <5days

week Jan1 3 4 7 8 10
week Jan8 2 6 6 8 10
week Jan15 3 4 3 1 10
.........

How can I use group by date range ..to get the above results..

Hope I am making sense..

Thanks

Originally Posted by krishnaindia2007

You can do it using decode function.
Post your table structure and sample data to check the output.

Here is the table structure

create table PROMISE
(
POLICY NUMBER(7),
CURR_DTE DATE,
INPUT_DTE DATE,
RECEIVED_DTE DATE
)

sample data : contains data for 2 weeks..

POLICY CURR_DTE INPUT_DTE RECEIVED_DTE
1239 4/26/2008 4/25/2008 4/25/2008
1235 4/22/2008 4/22/2008 4/23/2008
1234 4/21/2008 4/23/2008 4/25/2008
1238 4/25/2008 4/25/2008 4/26/2008
1237 4/24/2008 4/24/2008 4/26/2008
1236 4/23/2008 4/24/2008 4/26/2008
1247 4/27/2008 4/23/2008 4/27/2008
1245 4/26/2008 4/25/2008 4/27/2008
1249 4/25/2008 4/25/2008 4/25/2008
1259 4/27/2008 4/24/2008 4/25/2008
1249 4/23/2008 4/23/2008 4/27/2008
1269 5/2/2008 4/30/2008 5/1/2008
1289 5/1/2008 4/29/2008 4/30/2008
1295 5/1/2008 4/25/2008 4/27/2008
1297 4/30/2008 4/23/2008 4/27/2008
1286 4/30/2008 4/24/2008 4/26/2008
1287 4/29/2008 4/24/2008 4/26/2008
1218 4/29/2008 4/25/2008 4/30/2008
1214 4/29/2008 4/29/2008 4/30/2008
1215 4/28/2008 4/28/2008 4/29/2008


Sample output :
week <= 1day <=2day <=3day <=4day <=5day
week 20 April 08 4 8 8 9 9
week 27 April 08 4 7 7 10 11

I could do for one week :
my query is :
select 'week ' || to_char('20-APR-2008')
,SUM(case when diff <=1 THEN 1
ELSE 0
END ) "<=1day "
,SUM(case when diff <=2 THEN 1
ELSE 0
END ) "<=2days "
,SUM(case when diff <=3 THEN 1
ELSE 0
END ) "<=3days "
,SUM(case when diff <=4 THEN 1
ELSE 0
END ) "<=4days "
,SUM(case when diff <=5 THEN 1
ELSE 0
END ) "<=5days "
FROM (select policy, (received_dte - input_dte ) diff
from promise
where curr_dte between '20-APR-2008' and '26-APR-2008' );


I want this output in one single select .
Hope its clear now..
Thanks..

Your query for single week is also not correct

select 'week ' || to_char('20-APR-2008')
,SUM(case when diff <=1 THEN 1 ELSE 0 END ) "<=1day "
,SUM(case when diff <=2 THEN 1 ELSE 0 END ) "<=2days "
,SUM(case when diff <=3 THEN 1 ELSE 0 END ) "<=3days "
,SUM(case when diff <=4 THEN 1 ELSE 0 END ) "<=4days "
,SUM(case when diff <=5 THEN 1 ELSE 0 END ) "<=5days "
FROM (select policy, (received_dte - input_dte ) diff from Promise

Just write diff=5 not diff<=5

or else use the following query
SELECT 'WEEK' || to_char('20-APR-2008'),
sum(decode(diff,1,1,0)) "<=1day ",
sum(decode(diff,2,1,0)) "<=2days ",
sum(decode(diff,3,1,0)) "<=3days ",
sum(decode(diff,4,1,0)) "<=4days",
sum(decode(diff,5,1,0)) "<=5days "
FROM (select policy, (received_dte - input_dte ) diff
from promise
where curr_dte between '20-APR-2008' and '26-APR-2008');

To exetend it all weeks write a simple procedure

First find no of weeks using the following query
SELECT CEIL((MAX(INPUT_DTE) - MIN(INPUT_DTE))/7)
INTO V_NO_OF_WEEKS
FROM PROMISE;

Then use V_NO_OF_WEEKS as loop index.
FOR I IN 0..V_NO_OF_WEEKS-1
LOOP
Write your query here
END LOOP;

>>I want this output in one single select .

Use the following query

select
trunc(CURR_DTE,'WW'),
sum(decode((received_dte - input_dte ),0,1,0)) " 0 days" ,
sum(decode((received_dte - input_dte ),1,1,0)) "<=1days ",
sum(decode((received_dte - input_dte ),2,1,0)) "<=2days ",
sum(decode((received_dte - input_dte ),3,1,0)) "<=3days ",
sum(decode((received_dte - input_dte ),4,1,0)) "<=4days ",
sum(decode((received_dte - input_dte ),5,1,0)) "<=5days ",
sum(decode((received_dte - input_dte ),6,1,0)) "<=6days "
from promise
group by trunc(CURR_DTE,'WW')

Originally Posted by krishnaindia2007

>>I want this output in one single select .

Use the following query

select
trunc(CURR_DTE,'WW'),
sum(decode((received_dte - input_dte ),0,1,0)) " 0 days" ,
sum(decode((received_dte - input_dte ),1,1,0)) "<=1days ",
sum(decode((received_dte - input_dte ),2,1,0)) "<=2days ",
sum(decode((received_dte - input_dte ),3,1,0)) "<=3days ",
sum(decode((received_dte - input_dte ),4,1,0)) "<=4days ",
sum(decode((received_dte - input_dte ),5,1,0)) "<=5days ",
sum(decode((received_dte - input_dte ),6,1,0)) "<=6days "
from promise
group by trunc(CURR_DTE,'WW')

Thanks for your patience.......I am close to my out put but
I needed the output for the weeks starting from monday..
the above query gives the output starting form tuesday..


Thanks,
Vijay

>>the above query gives the output starting form tuesday..


WW->Considers same day of the week as the first day of the year.
As 01-01-2008 is tuesday, it treats it as starting day.

Use next_day function as follows

select
next_day(trunc(CURR_DTE,'WW'),'MONDAY'),
sum(decode((received_dte - input_dte ),0,1,0)) " 0 days" ,
sum(decode((received_dte - input_dte ),1,1,0)) "<=1days ",
sum(decode((received_dte - input_dte ),2,1,0)) "<=2days ",
sum(decode((received_dte - input_dte ),3,1,0)) "<=3days ",
sum(decode((received_dte - input_dte ),4,1,0)) "<=4days ",
sum(decode((received_dte - input_dte ),5,1,0)) "<=5days ",
sum(decode((received_dte - input_dte ),6,1,0)) "<=6days "
from promise
group by next_day(trunc(CURR_DTE,'WW'),'MONDAY')

Thanks Krishna......

I got the output as desired...

I need one more thing...

I have two select stataments

1) Select sum(decode((received_dte - input_dte ),0,1,0)) " 0 days" ,
,sum(decode((received_dte - input_dte ),1,1,0)) "<=1days "
..................
where date between 'curr_dte' and 'curr_dte'
--this will give for output (one row) for date range specified as input...

2) Select sum(decode((received_dte - input_dte ),0,1,0)) " 0 days" ,
,sum(decode((received_dte - input_dte ),1,1,0)) "<=1days "
..................
group by week date

Is there any way of customizing these two select statmnts into one...?

If I pass two dates...I should get output for date range...
or if I want weekly .....then I want output as second select....

I will be getting inputs from web as

input 1) weekly or
2) specified : date1 and date 2

output is sys_refcursor.............

Thanks,
Vijay

>>input 1) weekly or 2) specified : date1 and date 2

If option = 1 then
First set of statements -- weekly
else
second set statemetns -- specified
End if;

[QUOTE=krishnaindia2007;29570]>>input 1) weekly or 2) specified : date1 and date 2

Thanks Krishna,

I was thinking to do it using one select but for different condition..
select ,,,,,,,,,,,,,
from promise
where (range_ind = 'S'
AND (curr_dte BETWEEN date1 AND date2) )
OR ( range_ind = 'W' )
group by trunc(CURR_DTE,'D') ;

group by must be used for one ange_ind = 'W'...

I think its not possible in this way....

Thanks a lot.......
Vijay

You can do it in a signle statement using case