2012-06-12

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);





Show more