Tuesday, June 12, 2012

Calender with single sql statement

Found this cool script from a oracle blog.

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH, week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( select dt, (case when dt >=
to_date(to_char(dt, 'dd/')||'12/'||to_char(sysdate,'yyyy'),'dd/mm/yyyy') and wk = '01' then '53' else wk end) week
from(
SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt, TO_CHAR(TRUNC(SYSDATE,'y')-1+ROWNUM+1,'iw') wk
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
)
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), week)
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);


No comments: