달력 1
환경 : 오라클 12C
WITH MONTH_DAY
AS
(
SELECT ( TRUNC( TO_DATE( :AV_DAY , 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) - 1 )) + LEVEL - 1 DD
FROM DUAL
CONNECT BY LEVEL <= ( ( LAST_DAY( TO_DATE(:AV_DAY, 'YYYYMMDD') ) + ( 7 - TO_CHAR( LAST_DAY( TO_DATE(:AV_DAY, 'YYYYMMDD') ), 'D' ))) -- 종료일
- ( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) - 1 )) + 1 ) -- 시작일
)
, MONTH_DAY_WEEK
AS
(
SELECT DD
, TRUNC( (TO_NUMBER(DD - ( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) ))) - 1 )/ 7) WEEK
, TO_CHAR( DD, 'DY' ) DAYOFWEEK
, TO_CHAR( DD, 'DD' ) VAL1
FROM MONTH_DAY
)
SELECT TO_CHAR( MIN(DD), 'YYYYMMDD') || '~' || TO_CHAR( MAX( DD ), 'YYYYMMDD') FROMTO
, WEEK
, MAX("'일'") "SUN"
, MAX("'월'") "MON"
, MAX("'화'") "TUE"
, MAX("'수'") "WED"
, MAX("'목'") "THU"
, MAX("'금'") "FRI"
, MAX("'토'") "SAT"
FROM MONTH_DAY_WEEK
PIVOT
(
MAX( VAL1 ) FOR DAYOFWEEK IN ( '일', '월', '화', '수', '목', '금', '토' )
) A
GROUP BY WEEK
ORDER BY WEEK
;
:AV_DAY := '20191001'
