If you had a table NON_WORKING_DAYS with both holidays and weekends in it,
and possibly a reason code if you use the holidays table for any other
purpose, would that make things easier?
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"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 - 09:29:47 CDT
