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

From: Michael Murphy <mike_murphy10_at_hotmail.com>
Date: 30 Jul 2003 06:07:10 -0700
Message-ID: <6cfe2956.0307300507.36289e19@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 - 08:07:10 CDT