2008. 10. 8. 15:34

[oracle]한달 달력 출력 쿼리

select
      WEEKS,
      sum(decode(D,'1', DAYNUM)) as SUN,
      sum(decode(D,'2', DAYNUM)) as MON,
      sum(decode(D,'3', DAYNUM)) as TUE,
      sum(decode(D,'4', DAYNUM)) as WED,
      sum(decode(D,'5', DAYNUM)) as THU,
      sum(decode(D,'6', DAYNUM)) as FRI,
      sum(decode(D,'7', DAYNUM)) as SAT
from (
  select
        ROWNUM                  as DAYNUM,
        to_char(to_date(b.YYMM||lpad(ROWNUM,2,'0'),'YYYYMMDD'), 'D') as D,
        cast(to_char(to_date(b.YYMM||lpad(rownum,2,'0'),'YYYYMMDD'), 'W') as number) +
        decode(
            sign(cast(to_char(to_date(b.YYMM||lpad(ROWNUM,2,'0'),'YYYYMMDD'), 'D') as NUMBER)
            -cast(to_char(to_date(b.YYMM||'01','YYYYMMDD'), 'D') as NUMBER)),
            0, -1, 1, -1, 0)  +1  as WEEKS
  from dual cross join (select '200510' as YYMM from dual) b
  connect by level<=to_number(to_char(last_day(to_date(b.YYMM,'YYYYMM')),'DD'))
)
group by WEEKS