create custom function for date difference excluding weekends and holidays in oracle sql

Question

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:

(calculate the day difference between startdate and enddate) -> (exclude the number of weekend days between startdate and enddate) -> (exclude the number of weekends between startdate and enddate)

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.

Thanks in advance!

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 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.

Answer 1

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:

From my answer here:

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