Solved

ORACLE PL-SQL - Business days calculation

Posted on 2014-12-09
12
3,597 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
12 Comments
 
LVL 77

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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 200 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 13

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

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 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 100 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 13

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows how to recover a database from a user managed backup
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

726 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