[Oracle] 주말과 공휴일 제외한 평일 날짜 계산 방법

오라클 SQL에서 주말과 공휴일을 제외하고 평일만 구하기 위해서는, TO_CHAR 함수를 사용하여 일요일과 토요일을 제외하고, 공휴일 마스터 테이블과 조인하여 공휴일을 제외하면 된다. CONNECT BY 절을 사용하여 시작일자와 종료일자를 입력하면 해당 기간 동안의 모든 날짜를 조회할 수 있는 날짜 뷰를 만들 수 있다. 공휴일 마스터는 실무에서는 실제 테이블로 생성하여 연도 별로 공휴일을 입력하여 사용하고 있다.

 

목차 지정된 기간에 공휴일 표시하기 지정된 기간에 주말, 공휴일 제외하기

 

지정된 기간에 공휴일 표시하기

WITH date_list AS (
    SELECT TO_DATE('2024-05-01', 'YYYY-MM-DD') + LEVEL - 1 AS work_date
      FROM dual
   CONNECT BY LEVEL <= TO_DATE('2024-05-31', 'YYYY-MM-DD') - TO_DATE('2024-05-01', 'YYYY-MM-DD') + 1
),
holidays AS (
    SELECT TO_DATE('2024-05-01', 'yyyy-mm-dd') hldy_date, '근로자의날'    hldy_desc FROM dual UNION ALL
    SELECT TO_DATE('2024-05-05', 'yyyy-mm-dd') hldy_date, '어린이 날'     hldy_desc FROM dual UNION ALL    
    SELECT TO_DATE('2024-05-06', 'yyyy-mm-dd') hldy_date, '대체공휴일'    hldy_desc FROM dual UNION ALL   
    SELECT TO_DATE('2024-05-15', 'yyyy-mm-dd') hldy_date, '부처님 오신날' hldy_desc FROM dual 
)

SELECT a.work_date
     , b.hldy_desc
  FROM date_list a
  LEFT OUTER JOIN holidays b
    ON a.work_date = b.hldy_date
 ORDER BY a.work_date

 

 

특정 기간의 시작일자와 종료일자를 지정하여 날짜 뷰(date_list)를 생성하여, 공휴일 마스터와 Outer Join 하면 지정한 기간의 날짜에 공휴일을 표시할 수 있다. 날짜 뷰에서 시작일자와 종료일자를 조정하면 더 많은 기간을 조회할 수 있다.

 

위의 예시를 만들기 위해서 공휴일 마스터(holidays)를 WITH절로 생성하였지만, 실무에서는 공휴일 마스터 테이블을 생성하여 사용한다. 공휴일 마스터 테이블 구조는 아래를 참고하면 된다.

 

공휴일 마스터 테이블 예시

-- 공휴일 마스터 테이블
CREATE TABLE holidays (
    hldy_date DATE PRIMARY KEY,
    hldy_desc VARCHAR2(100)
);

 

위와 같이 공휴일 마스터 테이블을 생성하고, 해당 연도의 공휴일을 미리 입력하여 사용하면 된다.

 

지정된 기간에 주말, 공휴일 제외하기

WITH date_list AS (
    SELECT TO_DATE('2024-05-01', 'YYYY-MM-DD') + LEVEL - 1 AS work_date
	  FROM dual
   CONNECT BY LEVEL <= TO_DATE('2024-05-31', 'YYYY-MM-DD') - TO_DATE('2024-05-01', 'YYYY-MM-DD') + 1
),
holidays AS (
    SELECT TO_DATE('2024-05-01', 'yyyy-mm-dd') hldy_date, '근로자의날'    hldy_desc FROM dual UNION ALL
    SELECT TO_DATE('2024-05-05', 'yyyy-mm-dd') hldy_date, '어린이 날'     hldy_desc FROM dual UNION ALL    
    SELECT TO_DATE('2024-05-06', 'yyyy-mm-dd') hldy_date, '대체공휴일'    hldy_desc FROM dual UNION ALL   
    SELECT TO_DATE('2024-05-15', 'yyyy-mm-dd') hldy_date, '부처님 오신날' hldy_desc FROM dual 
)

SELECT a.work_date
     , b.hldy_desc
  FROM date_list a
  LEFT OUTER JOIN holidays b
    ON a.work_date = b.hldy_date
 WHERE TO_CHAR(a.work_date, 'D') NOT IN (1, 7)  -- 주말 제외 (1: 일요일, 7: 토요일)
   AND b.hldy_desc IS NULL -- 공휴일 제외
 ORDER BY a.work_date

 

 

날짜 뷰와 공휴일 마스터를 Outer Join 한 데이터에서 TO_CHAR 함수를 사용하여 일요일(1), 토요일(7)을 제외하고, 공휴일 명이 있는 날짜를 제외하였다. 위의 조인 조건에 필요한 테이블을 추가로 Outer Join 한다면 원하는 데이터를 얻을 수 있을 것이다.

 

날짜 뷰와 공휴일 마스터를 Outer Join을 하지 않고 NOT IN, NOT EXISTS를 사용하여 공휴일을 제외할 수도 있다.

 

SELECT a.work_date
  FROM date_list a
 WHERE TO_CHAR(a.work_date, 'D') NOT IN (1, 7)  -- 주말 제외 (1: 일요일, 7: 토요일)
   AND a.work_date NOT IN (SELECT hldy_date FROM holidays) -- 공휴일 제외
 ORDER BY a.work_date
;

SELECT a.work_date
  FROM date_list a
 WHERE TO_CHAR(a.work_date, 'D') NOT IN (1, 7)  -- 주말 제외 (1: 일요일, 7: 토요일)
   AND NOT EXISTS (SELECT 1 
                     FROM holidays b
                    WHERE b.hldy_date = a.work_date) -- 공휴일 제외
 ORDER BY a.work_date

 

[Oracle] 시작일자~종료일자 기간내 모든 날짜(일자) 구하기

[Oracle] 오라클 CONNECT BY 사용법 (계층형 쿼리)

[Oracle] 오라클 WITH 절 사용법 및 동작방식 (WITH 여러개)