Date grouping in Oracle Discoverer

[davehill1974]

Hi,
I have a crosstab query that counts how many jobs are on a status by date I need to group the date by 1-7 8-14 15-21 22-28 & 29+ I have tried to use width_bucket but i cannot get this to work.

Does anybody know of a way to group date in increments of 7 then 29+

Thanks

 

[SantaMufasa]

Dave,

I presume that what you are wanting is something to crosstab total the numbers of rows whose day of the month (regardless of month and year) fall into the 7-day splits that you specify. If so, the following code (or a variant) should take care of business for you. (I haven't a copy of Oracle Discover to test with, but you can run this in SQL*Plus, then tailor for OD.)

Section 1 -- Some sample data:

select start_date from s_emp;

START_DAT
---------
01-FEB-06
08-MAR-90
17-JUN-91
07-APR-90
04-MAR-90
18-JAN-91
14-MAY-90
07-APR-90
09-FEB-92
27-FEB-91
14-MAY-90
18-JAN-92
18-FEB-91
22-JAN-92
09-OCT-91
07-FEB-92
08-MAR-90
09-FEB-91
06-AUG-91
21-JUL-91
26-MAY-91
30-NOV-90
17-OCT-90
17-MAR-91
09-MAY-91

25 rows selected.

Section 2 -- Code that splits by day number in the month:

col a heading "1-7" format 999
col b heading "8-14" format 999
col c heading "15-21" format 999
col d heading "22-28" format 999
col e heading "29+" format 999
select count(decode(trunc((to_number(to_char(start_date,'dd'))-1)/7),0,1,null)) a
      ,count(decode(trunc((to_number(to_char(start_date,'dd'))-1)/7),1,1,null)) b
      ,count(decode(trunc((to_number(to_char(start_date,'dd'))-1)/7),2,1,null)) c
      ,count(decode(trunc((to_number(to_char(start_date,'dd'))-1)/7),3,1,null)) d
      ,count(decode(trunc((to_number(to_char(start_date,'dd'))-1)/7),4,1,null)) e
from s_emp
/

1-7 8-14 15-21 22-28  29+
--- ---- ----- ----- ----
  6    8     7     3    1

Let us know if this does what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[davehill1974]

Thanks I will go and try this now!!

 

[davehill1974]

OK I see that my description of the issue is poor... I apologise. I have created a calculated item called daysold which is CURRENT_DATE-statusdate from this (daysold) I need to group by count of a status that falls within the 1-7 8-14 etc so the output will appear similar to..

1-7 8-14 15-21 22-28 29+
status1 25 156 12 57 32
status2 95 65 132 21 54
status3 87 78 84 65 01

So in essence the numbers in the cross tab are how many days a job has been sitting on each individual status for.
Is this even possible in Discoverer / Oracle?

Im an Informix boy migrating over to the 21st Century!!

Thanks for your help.