?
Solved

ORACLE PL-SQL - Business days calculation

Posted on 2014-12-09
12
Medium Priority
?
6,003 Views
Last Modified: 2014-12-16
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.
0
Comment
Question by:saved4use
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40489544
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 total points
ID: 40489549
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40489583
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 800 total points
ID: 40489647
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
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40489801
What about this one here?! I'd give it a shot ;-)

http://www.dba-oracle.com/t_list_of_public_holidays.htm
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 800 total points
ID: 40489857
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
 

Author Comment

by:saved4use
ID: 40489872
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 total points
ID: 40489878
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 400 total points
ID: 40489918
"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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40502585
why the B?

a penalty grade isn't appropriate here
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40502688
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question