Re: Calculate the date + a number of working days. HOW?

Here is a solution I have used and is _close_ to what you need. It may not be the most elegant, there is no error handling to speak of, but it works well:

I created a table called CALENDAR as follows:

CREATE TABLE HR.CALENDAR
(

    FULL_DATE DATE NOT NULL,
    DAY_OF_WEEK VARCHAR2(3) NOT NULL,
    DAY_TYPE VARCHAR2(1) NOT NULL
) etc...

I populated the FULL_DATE and DAY_OF_WEEK by using Excel's autofill feature, and then loading the data. DAY_TYPE is one of these values : B (Business day), W (weekend day) H (Holiday). This technique allows me to customize the holidays for each year.

I then created the following functions:

/* This function accepts a date, and desired number of business days. It returns the desired business date. */

CREATE OR REPLACE FUNCTION Target_Date (start_date varchar2, num_days in number)
RETURN DATE IS
final_date DATE;
i NUMBER := 0;
CURSOR c IS
SELECT full_date FROM calendar WHERE day_type='B' AND full_date >= to_date(start_date,'dd-mon-yyyy'); BEGIN
OPEN c;
LOOP
FETCH c INTO final_date;
i := i+1 ;
EXIT WHEN i > num_days;
END LOOP;
RETURN (final_date);
CLOSE c ;
END Target_Date;

/* This function returns the number of business days between two dates */

CREATE OR REPLACE FUNCTION num_Business_Days(start_date in varchar2, end_date in varchar2)
return number is
countBusiness number := 0;
begin
/* start date must be earlier than end date */ if to_date(end_date,'mm/dd/yyyy') - to_date(start_date,'mm/dd/yyyy') < 0 then
return (-1);
end if;
select count(*) into countBusiness
from calendar
where full_date between to_date(start_date,'mm/dd/yyyy') and to_date(end_date,'mm/dd/yyyy')
and day_type='B';
return (countBusiness-1);
end;

HTH "Michael Murphy" <mike_murphy10_at_hotmail.com> wrote in message news:6cfe2956.0307300507.36289e19_at_posting.google.com...
> I am disgraced by my way of calculating a date + a number of working
> days (involving a loop and running very slowly). I will post my 'code'
> if I really have to but don't want to show it at the moment because.
>
> Parts of my code is below. All I want to do is take a date and add a
> number of working days to it. Working days are days that are not
> Saturday or Sunday and not in the table holidays. I can find lots of
> examples calculating the number of working days between two dates but
> this isn't what I want.
>
> Any ideas on the code for the function "daysplus" I have put below.
>
> Thank you
> Michael
>
>
> create table holidays(holiday date);
>
> --wednesday
> insert into holidays values ('01-jan-2003');
>
> --tursday
> insert into holidays values ('02-jan-2003');
>
> --friday
> insert into holidays values ('03-jan-2003');
>
> --tuesday
> insert into holidays values ('07-jan-2003');
>
> create function daysplus(startdate in date,daystoadd number) as date
> is
> begin
> --some stuff in here to add "daystoadd" working days (ie days that
> are not Sat
> --or Sun or in the table "holidays" to startdate
> end;
>
>
> so that:
>
> select daysplus(to_date('30-dec-2002'),1) from dual;
> -- returns 06-jan-2003 --monday
>
> select daysplus(to_date('01-jan-2003'),1) from dual;
> -- returns 06-jan-2003 --monday
>
> select daysplus(to_date('02-jan-2003'),1) from dual;
> -- returns 06-jan-2003 --monday
> .
> .
> . etc
> .
> .
> select daysplus(to_date('06-jan-2003'),1) from dual;
> -- returns 08-jan-2003 --wednesday
>
> select daysplus(to_date('07-jan-2003'),1) from dual;
> -- returns 08-jan-2003 --wednesday
>
> select daysplus(to_date('08-jan-2003'),1) from dual;
> -- returns 09-jan-2003 --thursday
>
> select daysplus(to_date('08-jan-2003'),2) from dual;
> -- returns 10-jan-2003 --friday
>
> select daysplus(to_date('08-jan-2003'),3) from dual;
> -- returns 13-jan-2003 --monday
Received on Wed Jul 30 2003 - 10:47:44 CDT