Wednesday, August 26, 2015

Oracle Fiscal Year Calendar Query

SELECT  
    LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH
    ,TO_NUMBER (week) week
    , "Sun"
    , "Mon"
    , "Tue"
    , "Wed"
    , "Thu"
    , "Fri"
    , "Sat"
FROM
    (SELECT  
        TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
        TO_CHAR (dt + 1, 'iw') week,
        TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 1, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Sun",
        TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 2, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Mon",
        TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 3, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Tue",
        TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 4, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Wed",
        TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 5, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Thu",
        TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 6, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Fri",
        TO_NUMBER(MAX (DECODE (TO_CHAR (dt, 'd'), 7, LPAD (TO_CHAR (dt, 'fmdd'), 2) ) )) "Sat"
    FROM
        (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
         FROM all_objects
         WHERE ROWNUM <= ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12) - TRUNC (SYSDATE, 'y'))
         GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw')
    )

ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect