[SQL] 날짜 차이가 오라클 SQL에서 주말과 휴일을 제외하고 사용자 정의 함수를 만들

날짜 차이가 오라클 SQL에서 주말과 휴일을 제외하고 사용자 정의 함수를 만들

나는 오라클 SQL에서 사용자 지정 기능을 사용하여 주말 및 공휴일 제외 진수로 두 날짜 사이의 일 수를 계산해야합니다. 사이트에 비슷한 질문이 있습니다; 내가 볼 수 그러나 그들 중 누구도 사용자 정의 기능을 사용하여 진수로 출력을 요구하지 않습니다. 나는 진수를 필요로하는 이유는 나중에 사용 / 추출 시간 구성 요소 수있을 것입니다. 질문과 같이 이미 존재하는 경우, 단지 링크를 공유하시기 바랍니다.

나는 저자에 인터넷 덕분에있는 추가 콘텐츠의 도움으로 다음과 같은 함수를 작성했습니다. 내부 하위 쿼리는 별도로 잘 작동하지만, 전체 기능으로 작동하지 않습니다.

간단히, 아이디어는 다음과 같습니다

(STARTDATE 및 ENDDATE 사이 주말의 수를 제외)> - - (STARTDATE 및 ENDDATE 사이의 일의 차이를 계산)> (STARTDATE 및 ENDDATE 사이 주말의 수를 제외)

기호 "파일 끝을"가 발생했습니다 : 나는 기능을 저장하려고하면 오류 PLS-00103이 있습니다. 나는 기능에 이미 새로운 오전 때문에 아마 기본적인 뭔가가.

마지막으로, 또한 당신이 코드를보다 효율적으로하는 방법에 대한 제안이 있으면 알려 주시기 바랍니다.

사전에 감사합니다!

CREATE OR REPLACE FUNCTION  NET_WORKING_DAYS (startdate IN DATE, enddate IN DATE)
RETURN NUMBER IS 
  WORKINGDAYS_BETWEEN NUMBER;
BEGIN

SELECT

    -- number of days between startdate and enddate
      (
        SELECT (TO_DATE('20160831150000','YYYYMMDDHH24MISS')  - TO_DATE('20160801000000','YYYYMMDDHH24MISS') ) DAYS_BETWEEN 
        FROM DUAL
      )  
    -
    -- number of weekend days (after a given date)
      (
        SELECT COUNT(1)  WEEKEND_DAYS_BETWEEN
        FROM
        (
          SELECT
            TO_DATE('20160701000000','YYYYMMDDHH24MISS') + SEQ as day_date,     --2016/07/01 is a constant/given date for this formula
            TO_CHAR(TO_DATE('20160701000000','YYYYMMDDHH24MISS') + SEQ , 'D') day_of_week
          FROM
            (
                SELECT ROWNUM-1 SEQ
                FROM   ( SELECT 1 FROM  DUAL CONNECT BY LEVEL<= 365 * 5) --5 years
            )
          ORDER BY 1
        )
        WHERE day_of_week IN (6,7) 
          AND day_date > TO_DATE('20160801000000','YYYYMMDDHH24MISS')     --this should be replaced with startdate parameter
          AND day_date < TO_DATE('20160831000000','YYYYMMDDHH24MISS')      --this should be replaced with enddate parameter
      )  
    -
    -- number of holidays (after a given date)
      (
        SELECT COUNT(1)
        FROM HOLIDAYS
        WHERE HOLIDAY_DATE > TO_DATE('20160801000000','YYYYMMDDHH24MISS')     --this should be replaced with startdate parameter
          AND HOLIDAY_DATE < TO_DATE('20160831000000','YYYYMMDDHH24MISS')      --this should be replaced with enddate parameter
      )
INTO WORKINGDAYS_BETWEEN
FROM DUAL;

RETURN WORKINGDAYS_BETWEEN;
END NET_WORKING_DAYS;

** 편집-1 : 휴일이 이미 2016년 6월 30일는 휴가 날짜입니다, 20,160,801,000,000에서 20,160,831,000,000에 데이터베이스에서이 날짜 범위 휴일 테이블에 정의되어 있습니다.

해결법

1. ==============================

   1.당신은 주 일 수를 얻기 위해 매일 열거 할 행 생성기를 사용할 필요가 없습니다 - 그것은 간단한 계산을 사용하여 수행 할 수 있습니다 :

   당신은 주 일 수를 얻기 위해 매일 열거 할 행 생성기를 사용할 필요가 없습니다 - 그것은 간단한 계산을 사용하여 수행 할 수 있습니다 :

   여기에 내 대답에서 :

   CREATE FUNCTION getWorkingDays (
     in_start_date IN  DATE,
     in_end_date   IN  DATE
   ) RETURN NUMBER DETERMINISTIC
   IS
     p_start_date   DATE;
     p_end_date     DATE;
     p_working_days NUMBER;
     p_holiday_days NUMBER;
   BEGIN
     IF in_start_date IS NULL OR in_end_date IS NULL THEN
       RETURN NUll;
     END IF;
   
     p_start_date := LEAST( in_start_date, in_end_date );
     p_end_date   := GREATEST( in_start_date, in_end_date );
   
     -- 5/7 * ( Number of days between monday of the week containing the start date
     --         and monday of the week containing the end date )
     -- + LEAST( day of week for end date, 5 )
     -- - LEAST( day of week for start date, 5 )
     p_working_days := ( TRUNC( p_end_date, 'IW' ) - TRUNC( p_start_date, 'IW' ) ) * 5 / 7
                       + LEAST( p_end_date - TRUNC( p_end_date, 'IW' ), 5 )
                       - LEAST( p_start_date - TRUNC( p_start_date, 'IW' ), 5 );
   
     SELECT COALESCE(
              SUM(
                LEAST( p_end_date, holiday_date + INTERVAL '1' DAY )
                - GREATEST( p_start_date, holiday_date )
              ),
              0
            )
     INTO   p_holiday_days
     FROM   Holidays
     WHERE  HOLIDAY_DATE BETWEEN TRUNC( p_start_date )
                         AND     TRUNC( p_end_date )
     AND    HOLIDAY_DATE - TRUNC( HOLIDAY_DATE, 'IW' ) < 5;
   
     RETURN GREATEST( p_working_days - p_holiday_days, 0 );
   END;
   /
   

from https://stackoverflow.com/questions/44406680/create-custom-function-for-date-difference-excluding-weekends-and-holidays-in-or by cc-by-sa and MIT license