Thursday, July 7, 2011

Claender Query

SELECT   LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Su", "Mo", "Tu",
         "We", "Th", "Fr", "Sa"
    FROM (SELECT   TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
                   CASE
                      WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Dec%'
                      AND TO_CHAR (dt + 1, 'iw') = '01'
                         THEN '53'
                      WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Jan%'
                      AND TO_CHAR (dt + 1, 'iw') = '53'
                         THEN '.5'
                      ELSE TO_CHAR (dt + 1, 'iw')
                   END week,
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Su",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Mo",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Tu",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "We",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Th",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Fr",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Sa"
              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_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

No comments:

Post a Comment