등록한 휴일( 창립기념일등..) 과 주말(토,일)을 제외한 월의 마지막 업무일 N일 구하기
DB/Oracle
2024. 4. 22. 16:39
반응형
등록한 휴일( 창립기념일등..) 과 주말(토,일)을 제외한 달의 마지막 4일 구하기
WITH TB_HOLIDAY AS (
SELECT '20230703' AS ST_HOLIDAY_CD, '휴일1' ST_HOLIDAY_NM FROM DUAL
UNION SELECT '20230703' AS ST_HOLIDAY_CD, '휴일2' ST_HOLIDAY_NM FROM DUAL
UNION SELECT '20230713' AS ST_HOLIDAY_CD, '휴일3' ST_HOLIDAY_NM FROM DUAL
UNION SELECT '20230723' AS ST_HOLIDAY_CD, '휴일4' ST_HOLIDAY_NM FROM DUAL
UNION SELECT '20230726' AS ST_HOLIDAY_CD, '휴일5' ST_HOLIDAY_NM FROM DUAL
UNION SELECT '20230728' AS ST_HOLIDAY_CD, '휴일6' ST_HOLIDAY_NM FROM DUAL
)
select v_date
from
(
select v_date
from
(
SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) , 'yyyymmdd') v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ), 'D'), '1','Y','7','Y','X') isweeekd FROM DUAL
UNION
SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -1 , 'yyyymmdd') v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -1, 'D'), '1','Y','7','Y','X') isweeekd FROM DUAL
UNION
SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -2 , 'yyyymmdd') v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -2, 'D'), '1','Y','7','Y','X') isweeekd FROM DUAL
UNION
SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -3 , 'yyyymmdd') v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -3, 'D'), '1','Y','7','Y','X') isweeekd FROM DUAL
UNION
SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -4 , 'yyyymmdd') v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -4, 'D'), '1','Y','7','Y','X') isweeekd FROM DUAL
UNION
SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -5 , 'yyyymmdd') v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -5, 'D'), '1','Y','7','Y','X') isweeekd FROM DUAL
UNION
SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -6 , 'yyyymmdd') v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -6, 'D'), '1','Y','7','Y','X') isweeekd FROM DUAL
UNION
SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -7 , 'yyyymmdd') v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -7, 'D'), '1','Y','7','Y','X') isweeekd FROM DUAL
UNION
SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -8 , 'yyyymmdd') v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -8, 'D'), '1','Y','7','Y','X') isweeekd FROM DUAL
UNION
SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -9 , 'yyyymmdd') v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -9, 'D'), '1','Y','7','Y','X') isweeekd FROM DUAL
UNION
SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -10 , 'yyyymmdd') v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -10, 'D'), '1','Y','7','Y','X') isweeekd FROM DUAL
) b
WHERE b.isweeekd = 'X'
AND b.v_date not in ( select st_holiday_cd from TB_HOLIDAY where st_holiday_cd like substr( '202307', 1,6 ) || '%' )
ORDER BY b.v_date desc
)
WHERE rownum <= 4
반응형