Solved

ORACLE PL-SQL - Business days calculation

Posted on 2014-12-09
12
2,413 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 76

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 76

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 34

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

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 73

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 76

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 34

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 73

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now