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) ;
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
Subscribe to:
Posts (Atom)