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: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Fri, 02 Apr 2004 10:19:14 GMT
Message-ID: <C6bbc.136314$Wa.29264@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,



For adjustments for when the fiscal year does not start on the first of a
month, I propose the following:




VARIABLE first_day NUMBER


VARIABLE first_month NUMBER



EXEC :first_day   := 15


EXEC :first_month := 5



SPOOL fiscal_year



SELECT


      trans_date,
      'Fiscal' ||
         TO_CHAR(
            ROUND(
               ADD_MONTHS(
                  trans_date - :first_day + 1,
                  -5 - :first_month
               ),
               'YYYY'
            ),
            'YYYY'
      ) AS fiscal_year
   FROM
      test_dates




;



Thus, the results for when the fiscal year starts on 15th May, are:



TRANS_DAT FISCAL_YEA


--------- ----------
01-MAY-02 Fiscal2001
14-MAY-02 Fiscal2001
15-MAY-02 Fiscal2002
16-MAY-02 Fiscal2002
30-MAY-02 Fiscal2002
30-JUN-02 Fiscal2002
01-JUL-02 Fiscal2002
01-MAY-03 Fiscal2002
14-MAY-03 Fiscal2002
15-MAY-03 Fiscal2003
16-MAY-03 Fiscal2003
30-MAY-03 Fiscal2003
30-JUN-03 Fiscal2003
01-JUL-03 Fiscal2003





14 rows selected.



Being a stubborn type, I am sticking with the ROUND function.  This accounts
for the "-5 -:first_month" expression because I need to adjust the date back
to 1 July of the preceding year.  I use the date subtraction to adjust days
prior to the start of the fiscal year into the preceding month.



If I were to swallow my pride and consider the TRUNC function, I would
propose the following:


SELECT


      trans_date,
      'Fiscal' ||
         TO_CHAR(
            TRUNC(
               ADD_MONTHS(
                  trans_date - :first_day + 1,
                  1 - :first_month
               ),
            'YYYY'
            ),
            'YYYY'
         ) AS fiscal_year
   FROM
      test_dates




;



TRANS_DAT FISCAL_YEA


--------- ----------
01-MAY-02 Fiscal2001
14-MAY-02 Fiscal2001
15-MAY-02 Fiscal2002
16-MAY-02 Fiscal2002
30-MAY-02 Fiscal2002
30-JUN-02 Fiscal2002
01-JUL-02 Fiscal2002
01-MAY-03 Fiscal2002
14-MAY-03 Fiscal2002
15-MAY-03 Fiscal2003
16-MAY-03 Fiscal2003
30-MAY-03 Fiscal2003
30-JUN-03 Fiscal2003
01-JUL-03 Fiscal2003





14 rows selected.



This is probably simplier to understand because I am adjusting the start of
the fiscal to be the first of January for the current year.  Put :first_day
to 1 and :first_month to 1, and the innermost expression becomes
ADD_MONTHS( trans_date, 0)



Douglas Hawthorne
Received on Fri Apr 02 2004 - 04:19:14 CST












Original text of this message













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