Group by date range in plsql

[vijay.patil2005]

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

[krishnaindia2007]

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

[vijay.patil2005]

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..

[krishnaindia2007]

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;

[krishnaindia2007]

I tested following procedure based on test data provided by you . It is working fine.
Check the following procedure once.

create or replace procedure promise_test as

v_no_of_weeks number;
v_start_date date;
v_end_date date;
v_week varchar2(50);
v_zero number;
v_one number;
v_two number;
v_three number;
v_four number;
v_five number;
v_six number;
begin

select ceil((max(curr_dte) - min(curr_dte))/7), min(curr_dte), max(curr_dte)
into v_no_of_weeks ,v_start_date, v_end_date
from promise;

for i in 0..v_no_of_weeks-1
loop
v_zero := 0; v_one := 0 ; v_two := 0; v_three := 0; v_four := 0; v_five := 0; v_six := 0; v_five :=0;
select 'week' || to_char(v_start_date + (i *7)),
sum(decode(diff,0,1,0)) " 0 days" ,
sum(decode(diff,1,1,0)) "<=1days ",
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 ",
sum(decode(diff,6,1,0)) "<=6days "
into v_week, v_zero, v_one, v_two, v_three, v_four, v_five ,v_six
from
(select policy, (received_dte - input_dte ) diff from promise where curr_dte between (v_start_date + (i * 7)) and (v_start_date +(( i+1) *7) ));
dbms_output.put_line ( 'week ' || v_week || chr(10)|| ' 0 days ' || v_zero ||chr(10) || '<=1 day' || v_one||chr(10) ||'<=2 days ' || v_two ||chr(10) ||' <= 3days ' ||v_three||chr(10) ||' <= 4days '||v_four || chr(10) ||'<= 5days' ||v_five ||chr(10) || '<=6days '||v_six); end loop;
end ;
/

[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')

[vijay.patil2005]

>>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

[krishnaindia2007]

>>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')

[vijay.patil2005]

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

[krishnaindia2007]

>>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;

[vijay.patil2005]

[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

[krishnaindia2007]

You can do it in a signle statement using case