在oracle sql中为不包括周末和假期的日期差异创建自定义函数 [英] create custom function for date difference excluding weekends and holidays in oracle sql

问题描述

我需要通过在 Oracle 中使用自定义函数，将两个日期之间的天数计算为十进制，不包括周末和节假日SQL.网站上也有类似的问题；然而，正如我所看到的，他们都没有使用自定义函数要求输出为十进制.我需要小数的原因是为了能够在之后使用/提取时间分量.如果已经有这样的问题，请分享链接.

I need to calculate the number of days between two dates as decimal, excluding the weekends and holidays by using a custom function in Oracle SQL. There are similar questions on the site; however as I could see, none of them asks for an output as decimal using a custom function. The reason why I need a decimal is to be able to use/extract time component afterwards. If there is already a question like this, please just share the link.

借助我在互联网上找到的附加内容，尝试编写如下函数感谢作者.内部子查询单独工作正常，但不能作为一个整体工作.

Tried to write a function as below with the help of the additional content I found on the internet thanks to the author. The inner subqueries works fine seperately, but it doesn't work as a whole function.

简而言之，这个想法是:

In brief, the idea is:

(计算startdate和enddate之间的天差)->(不包括startdate和enddate之间的周末天数)->(不包括startdate和enddate之间的周末天数)

当我尝试保存该函数时，它给出了错误PLS-00103:遇到符号文件结束".由于我已经是函数的新手，可能缺少一些基本的东西.

When I try to save the function, it gives the error PLS-00103: Encountered the symbol "end-of-file". Since I am already new in functions maybe missing something basic.

最后，如果您对如何提高代码效率有任何建议，也请告诉我.

Lastly, also please let me know if you have suggestions on how to make the code more efficient.

提前致谢！

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;

**EDIT-1:已在数据库的 HOLIDAYS 表中定义了假期，此日期范围从 20160801000000 到 20160831000000 ，30.06.2016 是假期日期.

**EDIT-1: Holidays are already defined in HOLIDAYS table in the database and for this date range from 20160801000000 to 20160831000000 , 30.06.2016 is the holiday date.

推荐答案

你不需要使用行生成器来枚举每一天来获得工作日的天数——它可以使用一个简单的计算来完成:

You do not need to use a row generator to enumerate every day to get the number of week days - it can be done using a simple calculation:

来自我在这里的回答:

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;
/

这篇关于在oracle sql中为不包括周末和假期的日期差异创建自定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！