Conditional decode with dates

[sammybee]

Hi All,

I have a select query grouped by name, then area for example I want to have a 3 counts, 1 for those created less than 25 days, then 35 days then 56 days, is it poss to do this with a decode statement? e.g.

DECODE(count(created_date between sysdate -25 and sysdate)

????????????????

Many thanks

Sam

 

[kskid]

I would use the union

Select "0 - 25 ",
Count(created_date)
from table
where table.created_date between sysdate - 25
and sysdate
union
Select "26 - 56 ",
Count(created_date)
from table
where table.created_date between sysdate - 56
and sysdate

 

[sammybee]

Hey there,

can you tell me if it is possible with a union all to use group by on all?

Cheers

Sam

 

[SantaMufasa]

Sam,

I believe your easiest and most understandable code will be to use the Oracle "CASE" syntax (available in Oracle 8) instead of "DECODE". (Also, wouldn't you want to see the count of dates that are 56 days or greater?):

Section 1 -- Sample data:

select * from dates;

DT
---------
20-DEC-04
19-DEC-04
24-NOV-04
21-NOV-04
06-NOV-04
05-NOV-04
04-NOV-04
24-OCT-04
23-OCT-04

9 rows selected.

Section 2 -- Code and results:

col a heading "< 25|Days|old" format 99,999
col b heading "25-34|Days|old" format 99,999
col c heading "35-55|Days|old" format 99,999
col d heading "> 55|Days|old" format 99,999
select	 sum(case when sysdate-dt < 25 then 1 end) a
	,sum(case when sysdate-dt between 25 and 34.9999 then 1 end) b
	,sum(case when sysdate-dt between 35 and 54.9999 then 1 end) c
	,sum(case when sysdate-dt > 55 then 1 end) d
from dates
/

   < 25   25-34   35-55    > 55
   Days    Days    Days    Days
    old     old     old     old
------- ------- ------- -------
      2       2       3       2

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 15:54 (20Dec04) UTC (aka "GMT" and "Zulu"),
@ 08:54 (20Dec04) Mountain Time

 

[sammybee]

Perfect Dave,

Many thanks

Sam