在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:

(计算开始日期和结束日期之间的日差)->(不包括开始日期和结束日期之间的周末天数)->(不包括开始日期和结束日期之间的周末天数)

当我尝试保存该函数时，出现错误PLS-00103: Encountered the symbol "end-of-file".由于我已经是函数的新手，可能会缺少一些基本知识.

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;

**编辑-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屋！