달력2

환경 : 오라클 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 ) -- 시작일

), DATA_TMP

AS

(

SELECT LEVEL - 1 LV

FROM DUAL

CONNECT BY LEVEL <= 5

)

, DATA_CONT

AS

(

SELECT A.LV, B.DD, B.VAL1

FROM DATA_TMP A

,

(

SELECT TO_DATE( '20190303', 'YYYYMMDD') DD, 1 LV, '값1' VAL1 FROM DUAL UNION

SELECT TO_DATE( '20190303', 'YYYYMMDD') DD, 2 LV, '값2' VAL1 FROM DUAL UNION

SELECT TO_DATE( '20190403', 'YYYYMMDD') DD, 1 LV, '0403' VAL1 FROM DUAL UNION

SELECT TO_DATE( '20190303', 'YYYYMMDD') DD, 3 LV, '332' VAL1 FROM DUAL

)B

WHERE A.LV = B.LV(+)

)

, MONTH_DAY_WEEK

AS

(

SELECT A.DD

, TRUNC( (TO_NUMBER( A.DD - ( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) ))) - 1 )/ 7) WEEK

, TO_CHAR( A.DD, 'DY' ) DAYOFWEEK

, B.LV

, CASE WHEN B.LV = 0 THEN TO_CHAR( A.DD, 'DD' ) ELSE C.VAL1 END VAL1

FROM MONTH_DAY A, DATA_TMP B, DATA_CONT C

WHERE A.DD = C.DD(+)

AND B.LV = C.LV(+)

)

 

select *

from

(

SELECT TO_CHAR( MIN(DD), 'YYYYMMDD') || '~' || TO_CHAR( MAX( DD ), 'YYYYMMDD') FROMTO

, WEEK

, LV

, 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, LV

ORDER BY WEEK, LV

)

;

 

스케쥴이 포함된 달력 표시