ORACLE PL-SQL - Business days calculation

I am trying to calculate the business days (Mon-Fri) between two dates. I want to exclude weekends and holidays.
(a.complete_dt - a.appvl_dt) is not working as it it overlaps weekends/holidays.

Can someone provide a snippet of code for that?

Thanks for your tremendous assistance.
saved4useAsked:
Who is Participating?
 
sdstuberCommented:
assuming you'll put the logic into a function, it should be more efficient to loop in pl/sql  than looping through recursive sql (with or connect by)

You'll still need some sql to query your holiday table, but that's just one time.


CREATE OR REPLACE FUNCTION work_days_between(p_date1 IN DATE, p_date2 IN DATE)
    RETURN BINARY_INTEGER
IS
    v_holidays BINARY_INTEGER;
    v_weekdays BINARY_INTEGER := 0;
    v_date     DATE := TRUNC(p_date1);
BEGIN
    WHILE v_date <= p_date2
    LOOP
        IF TO_CHAR(v_date, 'Dy') NOT IN ('Sun', 'Sat')
        THEN
            v_weekdays := v_weekdays + 1;
        END IF;

        v_date := v_date + 1;
    END LOOP;

    SELECT COUNT(*)
      INTO v_holidays
      FROM your_holiday_table
     WHERE observed BETWEEN p_date1 AND p_date2;

    RETURN v_weekdays - v_holidays;
END;
0
 
slightwv (䄆 Netminder) Commented:
There are many versions of this out there if you look around.

Holidays are the tricky part.  Do you currently have a table that contains the valid holidays?  If not, you're probably going to need one.
0
 
slightwv (䄆 Netminder) Commented:
Here is an example I had around from a long time ago to count the days between two dates.  You can probably take that as a starting point and add a join to your holiday table.

Try this if Sunday in your NLS settings is 1:

select count(1) from
(
      select to_char(to_date('02/19/2010')+level,'D') day_of_week
      FROM dual
      CONNECT BY LEVEL < to_date('03/1/2010') - to_date('02/19/2010')
)
where day_of_week in (2,3,4,5,6)
/
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mark GeerlingsDatabase AdministratorCommented:
This "quick and dirty" approach can efficiently get you close to the number of weekdays between any two dates, expecially if they are at least a week apart:

select round((ending_date - starting_date) * 5 /7,0) "Weekdays" from dual;

Substitute actual column names or "to_date('a literal','[format mask')" for the "starting_date" and "ending_date" variables.

As sligthwv indicated, you will need a custom table to keep of your holidays, since these tend to be different for each organization.  You can add a sub-query from that custom holiday table something like this:

select count(*) from my_holidays
where holiday_date between starting_date and ending_date;

Then subtract this from the result of the other query I suggested, or the one slightwv suggested (which is more accurate, but may be slower).
0
 
sdstuberCommented:
This requires 11gR2 but pulls together the ideas above

WITH alldays(d)
     AS (SELECT DATE '2014-12-09' d
           FROM DUAL
         UNION ALL
         SELECT d + 1
           FROM alldays
          WHERE d < DATE '2015-01-04'),
     weekdays
     AS (SELECT *
           FROM alldays
          WHERE TO_CHAR(d, 'Dy') NOT IN ('Sat', 'Sun'))
SELECT COUNT(*)
  FROM weekdays
 WHERE d NOT IN (SELECT observed
                   FROM your_holiday_table)
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
What about this one here?! I'd give it a shot ;-)

http://www.dba-oracle.com/t_list_of_public_holidays.htm
0
 
saved4useAuthor Commented:
My workmate mentioned we have a function that exists. How do I call a function with a PL/SQL query? He said it's something like this: sf_workdays (StartDate in Date, EndDate in Date).
Tried and it's not working.
0
 
slightwv (䄆 Netminder) Commented:
In a select:
select sf_workdays(a.complete_dt, a.appvl_dt) from some_table;

>>Tried and it's not working.

What did you try?
Define "not working".
0
 
Mark GeerlingsDatabase AdministratorCommented:
"How do I call a function with a PL/SQL query?"

Which of these questions do you really mean:

"How do I call a function with [or in] a SQL query?"

"How do I call a function with [or in] a PL/SQL block (or procedure)?"

Slightwv gave you an example of calling a function in a SQL query.

In a PL\SQL block or procedure, you need to have a variable declared to accept the return value from the function, something like this:

declare
  v_days number;
begin
  v_days := sf_workdays(a.complete_dt, a.appvl_dt);
  dbms_output.put_line('Days: '||to_char(v_days));
end;
/

Note: depending on which tool you try this in, you may not see the output from the "dbms_output.put_line" command unless you run this command first:
set serveroutput on;
0
 
sdstuberCommented:
why the B?

a penalty grade isn't appropriate here
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
why the B?

a penalty grade isn't appropriate here

Yes, why the "B"?!

Maybe this guide will help: http://support.experts-exchange.com/customer/portal/articles/481419
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.