농부지기

SELECT YY_WEEK  --년간주차
     , MM_WEEK  --월간주차
     , SUM(DECODE(YOIL, '1', DD)) AS 일
     , SUM(DECODE(YOIL, '2', DD)) AS 월
     , SUM(DECODE(YOIL, '3', DD)) AS 화
     , SUM(DECODE(YOIL, '4', DD)) AS 수
     , SUM(DECODE(YOIL, '5', DD)) AS 목
     , SUM(DECODE(YOIL, '6', DD)) AS 금
     , SUM(DECODE(YOIL, '7', DD)) AS 토
  FROM (--2. 요일 및 주간 얻기
        SELECT TO_NUMBER(A.DD) AS DD
             , A.YM || A.DD    AS YMD
             , TO_CHAR(TO_DATE(A.YM || A.DD, 'YYYYMMDD'), 'D') AS YOIL
             , CEIL((ROWNUM + 7 - TO_NUMBER(TO_CHAR(TO_DATE(A.YM || A.DD, 'YYYYMMDD'), 'D'))) / 7) AS MM_WEEK
             , CEIL((TO_CHAR(TO_DATE(A.YM || A.DD,'YYYYMMDD'),'DDD') +
                     TO_CHAR(TO_DATE(SUBSTR(A.YM || A.DD,1,4)||'0101','YYYYMMDD'),'D') - 1) / 7) AS YY_WEEK
          FROM (--1. 해당년월에 대해 1~31일까지 생성
                SELECT '201403'             AS YM
                     , LPAD(ROWNUM, 2, '0') AS DD
                  FROM DUAL
                CONNECT BY LEVEL <= 31
                ) A
         WHERE A.DD <= TO_CHAR(LAST_DAY(TO_DATE(A.YM || '01', 'YYYYMMDD')), 'DD')
       )
GROUP BY YY_WEEK, MM_WEEK
ORDER BY YY_WEEK, MM_WEEK;