DB/ORACLE

[오라클/Oralce] 쿼리/query 주말 및 공휴일을 제외한 날짜 구하기, 영업일 구하기 or 주말 구하기

효기로그 2019. 10. 14. 15:54
반응형

본 포스팅은 쿼리를 통해서 주말/공휴일을 제외한 영업일을 구하는 방식을 소개합니다.

  • 1년 단위 주말 및 공휴일 제외한 날짜
  • 달의 특정 날짜에서 주말 및 공휴일을 제외한 날짜 ( 단 예외 상황이 존재 자세한 내용은 아래 설명 참고 )

1. ORACLE 쿼리

  • 1년 달력을 기준으로 주말 및 공휴일 제외 날짜를 구하는 쿼리입니다.
SELECT A.DT,
FROM ( SELECT TO_CHAR (SDT + LEVEL - 1, 'YYYY-MM-DD') DT,
              TO_CHAR (SDT + LEVEL - 1, 'D') D
       FROM (SELECT TO_DATE((TO_CHAR(SYSDATE, 'YYYY')||'01-01'),'YYYY-MM-DD') SDT,
                    TO_DATE((TO_CHAR(SYSDATE, 'YYYY')||'12-31'),'YYYY-MM-DD') EDT
              FROM DUAL)
       CONNECT BY LEVEL <= EDT - SDT + 1) A,
       (
         SELECT HOLIDAY DT
         FROM HOLI_TABLE
         WHERE TO_CHAR(HOLIDAY,'YYYY') >= TO_CHAR(SYSDATE,'YYYY')
       ) B
WHERE TO_DATE(A.DT,'YYYY-MM-DD') = B.DT(+)
AND A.D NOT IN ('1', '7')
AND B.DT IS NULL
ORDER BY A.DT ASC

 

  • 달의 특정 날짜에서 주말 및 공휴일을 제외한 날짜
SELECT * FROM (
       SELECT A.DT,A.D 
       FROM
          (SELECT TO_CHAR(SDT + LEVEL - 1, 'YYYY-MM-DD') DT
              ,TO_CHAR (SDT + LEVEL - 1, 'D') D
          FROM (
             SELECT TRUNC(SYSDATE,'MM') SDT
                   ,TO_DATE((TO_CHAR(SYSDATE, 'YYYY-MM')||'24'),'YYYY-MM-DD') EDT FROM DUAL
             )
          CONNECT BY LEVEL <= EDT - SDT + 1 ) A,
          (
             SELECT HOLIDAY DT
             FROM IS_DC15
             WHERE TO_CHAR(holiday,'YYYY') >= TO_CHAR(sysdate,'YYYY')
           ) B
       WHERE TO_DATE(A.DT,'YYYY-MM-DD') = B.DT(+)
       AND A.D NOT IN ('1', '7')
       ORDER BY A.DT DESC)
WHERE ROWNUM <= 3

* 공휴일은 입력된 테이블이 존재해야 합니다. 없다면 B라고 별칭을 준 조인부분을 제외하면

주말만 체크하여 영업일을 확인할 수 있습니다.

여기서, 해당 날짜의 영업일을 구하는 쿼리는 임시로 제 월급일인 24일 기준으로 영업일 3일전을 구하는 쿼리입니다.
인라인뷰로 사용되는 값이 해당 월의 1일부터 마지막일 까지이기 때문에... 예를들어 해당월의 3일의 영업일 4일전 같은 쿼리는 해당 쿼리에서 구할 수 없기 때문에 전체 날짜를 구하는 첫번째 쿼리를 사용하시면 될 것 같습니다. 

반대로 주말 및 공휴일을 구하는 쿼리입니다.

SELECT A.DT
FROM ( SELECT TO_CHAR (SDT + LEVEL - 1, 'YYYY-MM-DD') DT,
              TO_CHAR (SDT + LEVEL - 1, 'D') D
       FROM (SELECT TO_DATE((TO_CHAR(SYSDATE, 'YYYY')||'01-01'),'YYYY-MM-DD') SDT,
                    TO_DATE((TO_CHAR(SYSDATE, 'YYYY')||'12-31'),'YYYY-MM-DD') EDT
              FROM DUAL)
       CONNECT BY LEVEL <= EDT - SDT + 1) A,
       (
         SELECT HOLIDAY DT
         FROM HOLI_TABLE
         WHERE TO_CHAR(HOLIDAY,'YYYY') >= TO_CHAR(SYSDATE,'YYYY')
       ) B
WHERE TO_DATE(A.DT,'YYYY-MM-DD') = B.DT(+)
AND (
     A.D IN ('1', '7')
  OR B.DT IS NOT NULL 
);

위에서 실행 된 쿼리에서 약간 변형을 주어서 날짜값이 NOT IN ('1','7') 이 아닌

주말을 찾고 싶기 때문에 IN ('1', '7') 로 변경해줍니다. 사실 다들 아실거지만... 그래도 혹시 몰라서 작성했습니다.

반응형