Showing posts with label Query for Display Calander... Show all posts
Showing posts with label Query for Display Calander... Show all posts

Wednesday, August 8, 2012

Query for Display Calander.. in Oracle



    Query for Display Calander..                                                                 
                                                                     
                                                                     
                                             
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) ;