Re: Decode Question

Maybe you are missing something.
Decode CAN definitely handle comparisons and counts within a date range. Look:

   /* . example syntax:                                            */


                      /* .>DECODE(sal, GREATEST(LEAST(sal, 4000), 2000),


'med',       */


                      /* .>            GREATEST(LEAST(sal, 5000), 4001),


'hi',        */


                      /* .>            'other')


*/


                      /* .>

*/


                      /* .>If sal is between 2000 and 4000, 'med' returns.


*/


                      /* .>If sal is between 4001 and 5000, 'hi' returns.


*/


                      /* .>Otherwise, the default 'other' returns.


*/


                      /* .>

*/


                      /* .>Given sal is 3000:


*/


                      /* .>

*/


                      /* .>DECODE(3000, GREATEST(LEAST(3000, 4000), 2000),


'med',     */


                      /* .>             GREATEST(LEAST(3000, 5000), 4001),


'hi',      */


                      /* .>             'other')


*/


                      /* .>  -- LEAST(3000, 4000) = 3000


*/


                      /* .>  -- LEAST(3000, 5000) = 3000

Apply the same logic to date values.

Here it is using dates:

                     /*  AGE group 1 = dte_inst is null */
                      /* AGE group 2 =  dte_inst < 05/08/1985  */
                      /*  AGE group 3 = 05/08/1985 to 08/04/1989 */
                      /*  AGE group 4 = dte_inst >= 08/05/1989      */
                     , decode(
                          /* <<<<<<<<<<<<<< group 1 test >>>>>>>>>>>>>>> */
                           to_char(dte_inst,'j'),null,'1',
                          /* <<<<<<<<<<<<<< group 2 test >>>>>>>>>>>>>>> */
                          greatest(
                          least(to_number(to_char(dte_inst,'j')),

to_number(to_char(to_date('05/07/1985','mm/dd/yyyy'),'j'))
                           )

,to_number(to_char(to_date('01/01/1900','mm/dd/yyyy'),'j'))
                           ),'2',
                          /* <<<<<<<<<<<<<< group 3 test >>>>>>>>>>>>>>> */
                          greatest(
                          least(to_number(to_char(dte_inst,'j')),

to_number(to_char(to_date('08/04/1989','mm/dd/yyyy'),'j'))
                           )

,to_number(to_char(to_date('05/08/1985','mm/dd/yyyy'),'j'))
                           ),'3',
                          /* <<<<<<<<<<<<<< group 4 test >>>>>>>>>>>>>>> */
                          greatest(
                          least(to_number(to_char(dte_inst,'j')),

to_number(to_char(to_date('01/01/9999','mm/dd/yyyy'),'j'))
                           )

,to_number(to_char(to_date('08/05/1985','mm/dd/yyyy'),'j'))
                           ),'4',
                          /* <<<<<<<<<<<<<< default value>>>>>>>>>>>>>>> */
                                           '0')
                        Age_group

"Mike Dwyer" <dwyermj_at_co,larimer.co.us> wrote in message news:FeP%4.21$k42.9024_at_wdc-read-01.qwest.net...

> Decode only handles exact matches, not less-than/greater-than. Try this:
>
> select dwacct_op, sum(1),
> sum( decode( dwacct_goodtype, 'good',   1, 0)),
> sum( decode( dwacct_goodtype, 'better', 1, 0))
> from dwacct
> where sysdate > to_date( substr( dwacct_id,6,7)) + 30
> group by dwacct_op
> union
> select dwacct_op, 0,
> sum( decode( dwacct_goodtype, 'good',   1, 0)),
> sum( decode( dwacct_goodtype, 'better', 1, 0))
> from dwacct
> where sysdate <= to_date( substr( dwacct_id,6,7)) + 30
> group by dwacct_op
> /
>
> You may also need to specify the format of the date string:
>
> where sysdate <= to_date( substr( dwacct_id,6,7), 'MONDDYY') + 30 -- for
> example
>
> "Moosie" <mooseneck_at_my-deja.com> wrote in message
> news:8hmgj0$lmh$1_at_nnrp1.deja.com...
> > Sorry for another question, but I'm not a PL/SQL programmer at all, but
> > have to produce reports in it (WebDB) for an emergency assignment at my
> > job.  My question is that I'm looking to sum the number of entry
> > accounts made by each operator with dates less than 30 days from now.
> > (The account creation date is appended to the account number which is a
> > string; that's why I'm extracting part of a string and converting it to
> > a date.) If an account is open more than 30 days, I want to count them
> > per the operator who put them in the system.  I'm also wanting to count
> > the type of account it is since there are two different account choices.
> > With counting the old entries, I don't care what type of account it is,
> > I just want to know if it's old.
> >
> > This is what I have:
> >
> > select distinct dwacct_op,
> > sum(decode(to_date(substr(dwacct_id, 6, 7)),to_date(substr(dwacct_id, 6,
> > 7)) < sysdate - 30,  1,0)), sum(decode(dwacct_goodtype, 'good', 1,0)),
> > sum(decode(dwacct_goodtype, 'better', 1,0))
> > from dwacct
> > group by dwacct_op
> >
> >
> > I'm tearing my hair out about this.  I don't want to create two separate
> > reports where I have the sum of the number of entries using decode and
> > having the <sysdate - 30 in the where cause.  I would like all this info
> > on one report.
> >
> > Thank you!!
> > Moosie
> >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>