Create a custom fiscal calendar dataset in oracle

[anil.kumar]

This query will create a dataset for a 36 month period, showing the fiscal and calendar months, based on a corporation whose financial year starts on 1 April each year.
the output looks like the screenshot below

http://seemoredata.com/wiki/_media/tips:sql:fiscalcalendaroutput.png


SELECT YEAR, MONTH, MM, YEARMONTH,
FISCAL_YEAR, DISPLAY_YEAR,
FISCAL_PERIOD, FISCAL from
(SELECT
TO_CHAR( ADD_MONTHS(TRUNC(SYSDATE + 365,'Y'),ROWNUM-37),'YYYY') AS YEAR,
TO_CHAR( ADD_MONTHS(TRUNC(SYSDATE + 365,'Y'),ROWNUM-37),'MONTH') AS MONTH,
to_number(TO_CHAR( ADD_MONTHS(TRUNC(SYSDATE + 365,'Y'),ROWNUM-37),'MM') ) AS MM,
TO_CHAR( ADD_MONTHS(TRUNC(SYSDATE + 365,'Y'),ROWNUM-37),'YYYY Mon') AS YEARMONTH,
TO_CHAR( ADD_MONTHS(TRUNC(SYSDATE + 365,'Y'),ROWNUM-28),'YY') AS FISCAL_YEAR,
TO_CHAR( ADD_MONTHS(TRUNC(SYSDATE ,'Y'),ROWNUM-28),'YY')||'/'||TO_CHAR( ADD_MONTHS(TRUNC(SYSDATE + 365,'Y'),ROWNUM-28),'YY') AS DISPLAY_YEAR,
TO_CHAR( ADD_MONTHS(TRUNC(SYSDATE + 365,'Y'),ROWNUM-28),'MM') AS FISCAL_PERIOD,
TO_CHAR( ADD_MONTHS(TRUNC(SYSDATE + 365,'Y'),ROWNUM-28),'YYYY MM') AS FISCAL
FROM ALL_OBJECTS
WHERE ROWNUM <= 48
order by rownum desc ) FC ,
(select case when to_number(to_char(sysdate,'MM')) < 4 then to_date(to_char(sysdate,'YYYY')||'03','YYYYMM')
else to_date(to_char(add_months(sysdate,12),'YYYY')||'0 3','YYYYMM') end as finyearend
from dual) CURR_CAL
where to_date(FC.YEAR||FC.MONTH,'YYYYMM') <= CURR_CAL.finyearend
and to_date(FC.YEAR||FC.MONTH,'YYYYMM') >= add_months(CURR_CAL.finyearend, -35)