Re: Need query to group by date

Thanks, Just what I needed.

mhr_at_NOSPAMramboll-it.dk wrote:

> 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 - 15:37:30 CDT