| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: DECODE -- nah, use round
"Douglas Hawthorne" <douglashawthorne_at_yahoo.com.au> wrote in message
news:sN9bc.136226$Wa.101824_at_news-server.bigpond.net.au...
> "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
> news:f4CdnRPmrdFTf_HdRVn_iw_at_comcast.com...
> >
> > "Douglas Hawthorne" <douglashawthorne_at_yahoo.com.au> wrote in message
> > news:9l3bc.135902$Wa.44333_at_news-server.bigpond.net.au...
> > | "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
> > | news:rY-dnXDOqKbCNPHdRVn-iQ_at_comcast.com...
> > | >
> > | > "Douglas Hawthorne" <douglashawthorne_at_yahoo.com.au> wrote in message
> > | > news:nD1bc.135809$Wa.110151_at_news-server.bigpond.net.au...
> > | > | "Sherman H." <shung_at_earthlink.net> wrote in message
> > | > | news:kA0bc.11648$lt2.2675_at_newsread1.news.pas.earthlink.net...
> > | > | > I have to run a query to give a column a value based on a time
> > range.
> > | > Can
> > | > | I
> > | > | > use DECODE?
> > | > | >
> > | > | > select decode(trans_date, trans_date>='01-Jul-2002' and
> > | > | > trans_date<='30-Jun-2003','Fiscal2002', .....) as fiscal,
> > | > | > from. . .
> > | > | > where. . .
> > | > | >
> > | > | >
> > | > |
> > | > | Sherman,
> > | > |
> > | > | If you are using 9i and later, you can use the CASE clause
> > | > |
> > | > | SELECT
> > | > | trans_date,
> > | > | CASE
> > | > | WHEN trans_date BETWEEN '01-JUL-2001' AND '30-JUN-2002'
> > | > | THEN 'Fiscal2001'
> > | > | WHEN trans_date BETWEEN '01-JUL-2002' AND '30-JUN-2003'
> > | > | THEN 'Fiscal2002'
> > | > | WHEN trans_date BETWEEN '01-JUL-2003' AND '30-JUN-2004'
> > | > | THEN 'Fiscal2004'
> > | > | ELSE NULL
> > | > | END AS fiscal
> > | > | FROM
> > | > | test_dates
> > | > | ;
> > | > |
> > | > | TRANS_DAT FISCAL
> > | > | --------- ----------
> > | > | 30-JUN-02 Fiscal2001
> > | > | 01-JUL-02 Fiscal2002
> > | > | 30-JUN-03 Fiscal2002
> > | > | 01-JUL-03 Fiscal2004
> > | > |
> > | > | Douglas Hawthorne
> > | > |
> > | > |
> > | >
> > | > actually, i bet this could be handled with the date version of the
> ROUND
> > | > function
> > | >
> > | > any takers? i'm on my way out, but i'll post a solution tomorrow
that
> > will
> > | > translate any date to the proper fiscal year (unless someone else
gets
> > to
> > | it
> > | > first)
> > | >
> > | > ;-{ mcs
> > | >
> > | >
> > | Mark,
> > |
> > | You are right. It works in 9.2.0.1.
> > |
> > | SQL> SELECT trans_date, round( trans_date , 'YEAR' ) FROM test_dates;
> > |
> > | TRANS_DAT ROUND(TRA
> > | --------- ---------
> > | 30-JUN-02 01-JAN-02
> > | 01-JUL-02 01-JAN-03
> > | 30-JUN-03 01-JAN-03
> > | 01-JUL-03 01-JAN-04
> > |
> > | Douglas Hawthorne
> > |
> > |
> >
> > round for dates has been a'round' since probably v4 -- but we actually
> want
> > the trunc function
> >
> > so what we've got to do in this case is turncate to the start of the
> fiscal
> > year, which looks like it starts on July 1 for the OP
> >
> > so, we need to adjust by the 'offset' of the fiscal year, in this case 6
> > months (july - jan, or 7 - 1)
> >
> > i think this should do it (reformat output with a fixed font for
> > readability):
> >
> > SQL> SELECT adate
> > 2 ,ADD_MONTHS(adate, -6) adjusted
> > 3 ,TRUNC(ADD_MONTHS(adate, -6), 'year') truncated
> > 4 ,TO_CHAR(TRUNC(ADD_MONTHS(adate, -6), 'year'), 'YYYY')
> > fiscal_year
> > 5 FROM test_dates
> > 6 /
> > ...
> >
> > ADATE ADJUSTED TRUNCATED FISC
> > --------- --------- --------- ----
> > 01-JAN-04 01-JUL-03 01-JAN-03 2003
> > 01-FEB-04 01-AUG-03 01-JAN-03 2003
> > 01-MAR-04 01-SEP-03 01-JAN-03 2003
> > 01-APR-04 01-OCT-03 01-JAN-03 2003
> > 01-MAY-04 01-NOV-03 01-JAN-03 2003
> > 01-JUN-04 01-DEC-03 01-JAN-03 2003
> > 01-JUL-04 01-JAN-04 01-JAN-04 2004
> > 01-AUG-04 01-FEB-04 01-JAN-04 2004
> > 01-SEP-04 01-MAR-04 01-JAN-04 2004
> > 01-OCT-04 01-APR-04 01-JAN-04 2004
> > 01-NOV-04 01-MAY-04 01-JAN-04 2004
> > 01-DEC-04 01-JUN-04 01-JAN-04 2004
> > 01-JAN-05 01-JUL-04 01-JAN-04 2004
> > 01-FEB-05 01-AUG-04 01-JAN-04 2004
> > 01-MAR-05 01-SEP-04 01-JAN-04 2004
> > 01-APR-05 01-OCT-04 01-JAN-04 2004
> > 01-MAY-05 01-NOV-04 01-JAN-04 2004
> > 01-JUN-05 01-DEC-04 01-JAN-04 2004
> > 01-JUL-05 01-JAN-05 01-JAN-05 2005
> > 01-AUG-05 01-FEB-05 01-JAN-05 2005
> > 01-SEP-05 01-MAR-05 01-JAN-05 2005
> > 01-OCT-05 01-APR-05 01-JAN-05 2005
> > 01-NOV-05 01-MAY-05 01-JAN-05 2005
> > 01-DEC-05 01-JUN-05 01-JAN-05 2005
> >
> >
> > the logic is:
> > adjust the date with add_months by the offset of the fiscal year
> > -- july 1 is 6 months after jan 1, so subtract 6 months
> > truncate the adjusted date to the calendar year
> > extract the year portion of the adjusted truncated date with to_char
> >
> > looks like the correct results, unless i'm overlooking something
> >
> > but, then what adjustments are required if the fiscal year does not
start
> on
> > the 1st of a month?
> >
> > ;-{ mcs
> >
> >
> Mark,
>
>
>
>
>
My apologies. Your solution is correct.
Douglas Hawthorne Received on Fri Apr 02 2004 - 03:18:21 CST
 |
 |