Re: DECODE -- nah, use round

"Douglas Hawthorne" <douglashawthorne_at_yahoo.com.au> wrote in message news:C6bbc.136314$Wa.29264_at_news-server.bigpond.net.au...

...

| 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
|

and this has been another fine edition of 'Fun with Functions' ;-)

i always advocate coding solutions that have high reusability. usually that involves just a little more distillation of the problem -- i think this is a nice example of that

;-{ mcs Received on Fri Apr 02 2004 - 05:21:32 CST