Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Business hours calculation - weekends already excluded and how to exclude holidays ?

soufirJan 19 2016 — edited Jan 22 2016
CREATE OR REPLACE FUNCTION working_hours
in_start_dt   IN      DATE DEFAULT SYSDATE
in_end_dt   IN      DATE DEFAULT SYSDATE

)

RETURN NUMBER

DETERMINISTIC

IS

--      *************************************
--      **                                 **
--       **   b u s i n e s s _ h o u r s   **
--      **                                 **
--      *************************************

--      working_hours returns the number of work houts (8 am through 8 pm,
--      Monday through Friday) between in_start_dt and in_end_dt.
--      If in_start_dt > in_end_dt, the results will be <= 0.
--      Holidays are not considered.
d      NUMBER;                            -- Hours of either start_dt or end_dt after midnight
end_dt      DATE:= GREATEST (in_start_dt, in_end_dt);  -- In case dates were in wrong order
return_val NUMBER;                                  -- Total number of working hours
start_dt DATE:= LEAST(in_start_dt, in_end_dt);  -- In case dates were in wrong order

     holiday_cnt     NUMBER;

BEGIN

WITH all_days AS
(
      SELECT start_dt + LEVEL - 1 AS a_dt
      FROM dual
      CONNECT BY LEVEL <= 1 + TRUNC (end_dt) - TRUNC (start_dt)
   MINUS
      SELECT  trunc(calendar_day)
      FROM public_holiday
)
SELECT SUM (12)
INTO return_val
FROM all_days
WHERE TO_CHAR ( a_dt
           , 'Dy'
           , 'NLS_DATE_LANGUAGE = ''ENGLISH'''
           )   NOT IN ('Fri', 'Sat');
-- Adjust hours from start_dt, if necessary
IF  TO_CHAR ( start_dt
              , 'Dy'
          , 'NLS_DATE_LANGUAGE = ''ENGLISH'''
          ) NOT IN ('Fri', 'Sat')

    THEN

      SELECT  COUNT (*)
      INTO holiday_cnt
      FROM public_holiday
      WHERE TRUNC(calendar_day) between TRUNC(start_dt) and TRUNC(end_dt);
   IF  holiday_cnt > 0
   THEN
      d := 24 *  (start_dt - TRUNC (start_dt));
      IF  d >= 20
      THEN   -- Don't count start_dt itself
   return_val := return_val - 12;
      ELSIF  d > 8
      THEN-- Don't count part of start_dt
   return_val := return_val - (d - 8);
      END IF;
   END IF;

    END IF;

-- Adjust hours from end_dt, if necessary
IF  TO_CHAR ( end_dt
              , 'Dy'
          , 'NLS_DATE_LANGUAGE = ''ENGLISH'''
          ) NOT IN ('Fri', 'Sat')
THEN
      d := 24 *  (end_dt - TRUNC (end_dt));
      IF  d <= 8
      THEN   -- Don't count end_dt itself
           return_val := return_val - 12;
      ELSIF  d < 20
      THEN-- Don't count part of end_dt
           return_val := return_val - (20 - d);
      END IF;
END IF;

    

IF  in_start_dt > in_end_dt
THEN
      return_val := -return_val;
END IF;

RETURN return_val;
END working_hours

;

This post has been answered by Etbin on Jan 22 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 19 2016
Added on Jan 19 2016
30 comments
3,129 views