Re: Need query to group by date

Bob McConnell wrote:

> I have a table of ORDERS which has an OrderDate field and an OrderStatus
> field. I would like to query to get orders <30 days, 30 to 60days, 60
> to 90days and > 90 grouped by Orderstatus. How would I do that?
>
> thanks
> Bob

Bob McConnell wrote:

> I have a table of ORDERS which has an OrderDate field and an OrderStatus
> field. I would like to query to get orders <30 days, 30 to 60days, 60
> to 90days and > 90 grouped by Orderstatus. How would I do that?
>
> thanks
> Bob

Hi,

You should use the decode function in combination with the functions sign and sum.

Here is the complete select:

select orderstatus,

       sum(decode(sign(30-(sysdate-orderdate)),1,1,0)) less_30,

sum(decode(sign(60-(sysdate-orderdate)),1,decode(sign(30-(sysdate-orderdate)),1,0,1),0)) btwn_30_60,

sum(decode(sign(90-(sysdate-orderdate)),1,decode(sign(60-(sysdate-orderdate)),1,0,1),0)) btwn_60_90,

       sum(decode(sign(90-(sysdate-orderdate)),1,0,1)) over_90 from ord
group by orderstatus;

My exampel has the following date:

(c:\tmp) SQL> select * from ord order by orderstatus,orderdate;

ORDERSTATU ORDERDATE

---------- ---------
Canceled   13-MAY-00
Canceled   01-AUG-00
Canceled   21-AUG-00
Canceled   21-AUG-00
Canceled   21-AUG-00
Canceled   14-SEP-00
Canceled   14-SEP-00
Canceled   14-SEP-00
Canceled   14-SEP-00
Complete   12-JUN-00
Complete   02-JUL-00
Complete   02-JUL-00
Complete   02-JUL-00
Complete   01-AUG-00
Complete   01-AUG-00
Complete   01-AUG-00
INPROCESS  23-MAY-00
INPROCESS  23-MAY-00
INPROCESS  12-JUN-00
INPROCESS  12-JUN-00
INPROCESS  22-JUN-00
INPROCESS  02-JUL-00
INPROCESS  02-JUL-00
INPROCESS  01-AUG-00
INPROCESS  01-AUG-00
INPROCESS  06-SEP-00
INPROCESS  06-SEP-00

The select produces the following output:

ORDERSTATU LESS_30 BTWN_30_60 BTWN_60_90 OVER_90 ---------- --------- ---------- ---------- ---------

Canceled           7          1          0         1
Complete           0          3          3         1
INPROCESS          2          2          3         4

I think that should do the trick :-)

Have a nice day! Received on Fri Sep 15 2000 - 03:49:41 CDT