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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.