Calculate working days

Hello,
Does anyone know of a function or formula that can be used in BIRT to calculate the number of working days between a start_date and end_date? The start_date and end_dates are in an Oracle table. I need to be able to exclude weekend days and possibly holidays.

Any help is greatly appreciated.

thanks,
nancy

Find more posts tagged with

Comments

bhanley

Hi Nancy,

I think you are going to have to calculate that value manually. If you can off-load the work onto Oracle, there is a "work_days" function built into PL/SQL that will do all the heavy lifting for you. Otherwise use Java Script in the expression editor to render the working days. There are lots of places to do this cal. Since the implementation is not your question, let me know if you want some ideas.

Hope this helps,

--Brian

nancyw

Thanks for the tip, Brian. I have created a function in Oracle that returns the # of working days between 2 dates and I can use that in my select statement.

It works!

Thanks again,
Nancy

nuraniuscc

Hi Nancy,

I am also working on BIRT and I am also having a requirement to calculate the difference between 2 dates minus Weekends and Holdiays.

Do you mind sharing the SQL that you have working in Oracle?

You can reach me at 608-441-4408.

Thanks very much
Nurani Sivakumar

nancyw

Hi Nurani, 
I found 2 functions on the web - these only work for week days - not holidays. 
<a class='bbc_url' href='http://www.oracle.com/technology/oramag/code/tips2003/012603.html'>Calculating Working Days Without Using a Function</a> 
<a class='bbc_url' href='http://www.tek-tips.com/viewthread.cfm?qid=1226507&page=10'>Oracle: Oracle release - 8 and 8i - calculating the number of working days between 2 dates</a> 
 
I used the 1st one - only because I found it first. I haven't tried the 2nd one. Here is the SQL for the function I created. 
 
CREATE OR REPLACE FUNCTION FMAX_OWNER.fmax_workday (p_sDate DATE := NULL, p_eDATE DATE := NULL) 
RETURN NUMBER IS 
/************************************************************************ 
Name: fmax_workday 
Original Purpose: Return the # of work days between 2 dates 
 
Parameters: 
Input: p_sDate - a date, p_eDate - a date 
Return: the # of working days between the 2 dates 
 
******************************************************************************/ 
num_days number := 0; 
s_date DATE := TRUNC(p_sDate); 
e_date DATE := TRUNC(p_eDate); 
t_days number := 0; 
 
 
BEGIN 
 
IF (s_date IS NULL) THEN 
s_date := TRUNC(SYSDATE); 
END IF; 
 
 
IF (e_date IS NULL) THEN 
e_date := TRUNC(SYSDATE); 
END IF; 
 
IF (8-to_number(to_char(s_date,'D') )) > trunc(e_date - s_date)+1 
THEN t_days := 0; 
ELSE 
t_days := trunc( (trunc(e_date - s_date) - (8-to_number(to_char(s_date,'D') ))) / 7 ) + 1; 
END IF; 
 
IF mod(8-to_char(s_date,'D'),7) > trunc(e_date - s_date)-1 
THEN t_days := t_days + 0; 
ELSE 
t_days := t_days + trunc( (trunc(e_date-s_date) - (mod(8-to_char(s_date,'D'),7)+1)) / 7 ) + 1; 
END IF; 
 
num_days := (trunc(e_date - s_date) - t_days) + 1; 
 
RETURN num_days; 
END fmax_workday; 
/ 
Oh- I have to add 1 day at the end because I need to include the start date as day 1 
 
Then, you can do a select as follows: 
select start_date, end_date, fmax_workday(start_date,end_date) num_workdays from mytable 
 
Hope this helps. If you need to exclude holidays, too, you'll probably need a "holiday" table. Here's a link that might give you info on how to exclude holidays: 
<a class='bbc_url' href='http://database.ittoolbox.com/groups/technical-functional/sql-l/working-days-between-two-dates-431430'>Working days between two dates - Toolbox for IT Groups</a> 
 
Nancy