[ORACLE] 주말과 공휴일 제외한 근무일(평일) 날짜 구하기(CONNECT BY LEVEL)

 

화면단에서 날짜를 선택하여 저장할 때 선택한 날짜가 주말이나 공휴일일 경우 

그 다음 근무일(평일)로 돌려줘야하는 상황이 생겼다.

 

그래서 주말과 공휴일을 제외한 날짜를 구하는 쿼리를 작성해보았다!

---

* 2022년 9월 기준 날짜 입니다.

 

1. 이번달의 주말을 제외한 평일 날짜 구하기 

SELECT 
	TM.DATES -- 날짜
	,TM.DAYS -- 요일
FROM (	SELECT
			TO_CHAR(START_DT + LEVEL - 1, 'YYYY-MM-DD') DATES -- 날짜
			,TO_CHAR (START_DT + LEVEL - 1, 'D') DAYS -- 요일
		FROM (	SELECT 
					TRUNC(SYSDATE,'MM') START_DT -- 이번달 시작 년월일
					,TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'DD')) END_DT -- 마지막 날짜 
				FROM DUAL ) A
		CONNECT BY LEVEL <= A.END_DT ) TM -- 마지막 날짜까지 반복
WHERE TM.DAYS NOT IN ('1', '7') -- 일요일,토요일 제외
ORDER BY TM.DATES ASC

 

CONNECT BY LEVEL을 이용해 이번달의 시작날짜와 마지막날짜를 이용해 전체 날짜를 뽑고

1(일요일), 2(토요일)을 제외 시킨다.

 

결과

주말을 제외한 이번달 날짜

 

2. 공휴일 테이블에서 이번달 공휴일 구하기

테스트용 공휴일 테이블 (테스트를 위해 만든 간단한 테이블)

 

SELECT 
	HOLIDAY_YEAR || '-' || HOLIDAY_MONTH || '-' || HOLIDAY_DATE AS DATES
FROM TB_HOLIDAY
WHERE HOLIDAY_YEAR = TO_CHAR(TO_DATE(SYSDATE), 'YYYY')
AND HOLIDAY_MONTH = TO_CHAR(TO_DATE(SYSDATE), 'MM')

결과

 

3. 두개의 결과(평일, 저장된 공휴일)를 조인하여 공휴일을 제외한 날짜 구하기

SELECT 
	WEEKDAY.DATES
FROM 
	-- 주말 제외한 평일
	(SELECT 
		 TM.DATES
		,TM.DAYS 
	FROM (SELECT
			 TO_CHAR(START_DT + LEVEL - 1, 'YYYY-MM-DD') DATES
			,TO_CHAR (START_DT + LEVEL - 1, 'D') DAYS
			FROM (	SELECT 
						 TRUNC(TO_DATE(SYSDATE),'MM') START_DT
						,TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'DD')) END_DT FROM DUAL
				)
			CONNECT BY LEVEL <= END_DT ) TM
			WHERE TM.DAYS NOT IN ('1', '7')
			ORDER BY TM.DATES ASC) WEEKDAY
	-- DB에 저장된 공휴일
		LEFT JOIN (SELECT 
						HOLIDAY_YEAR || '-' || HOLIDAY_MONTH || '-' || HOLIDAY_DATE AS DATES
					FROM TB_HOLIDAY
					WHERE HOLIDAY_YEAR = TO_CHAR(TO_DATE(SYSDATE), 'YYYY')
					AND HOLIDAY_MONTH = TO_CHAR(TO_DATE(SYSDATE), 'MM')) HOLIDAY
	ON WEEKDAY.DATES = HOLIDAY.DATES 
WHERE HOLIDAY.DATES IS NULL
ORDER BY WEEKDAY.DATES ASC

 

결과