Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DECODE -- nah, use round

Re: DECODE -- nah, use round

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Thu, 1 Apr 2004 22:48:50 -0500
Message-ID: <f4CdnRPmrdFTf_HdRVn_iw@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 Received on Thu Apr 01 2004 - 21:48:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US