I'm just starting to try and get my head around this one for someone who wants the resultant code implemented like 'yesterday'.
We have a table custom populated with all the dates in the year that are not working days. Consquently we can't just discount Saturdays and Sundays as non working days. We also have custom Public and Business holidays included.
From a given input date and number of working days into the future, the users want a function that will return the actual working day date - referencing their custom list of non working days.
Anyone any ideas how to best formulate the resultant function for best performance (over neatness!)?
The chances are users will be using the function to perform query results for an input set of hundreds of thousands of rows.
I'd think about generating a two column table (probably an IOT) with the date and the number-of-working-days-since-some-arbitrary-origin. You would always get your answer with two look-up's in this table, whereas if you used the original table you'd have to look-up every date in the interval.
In physical terms, how about evaluating: work-day(date) from the db table, date(work-day) from an indexed array in memory?
The hard part of this problem is keeping the table(s) up to date far enough into the future - I've had cases where banks decided at short notice to close for an extra day around Chistmas/New-Year.
It seems to me that a list of non-working days is doing you no favours here -- what you really need is a list of working days (you could do this dynamically of course by minus'ing your non-working days from a list of all days, but it wouldn't perform as well of course).
Anyway, if you had such a list then you could do the following ...
create table date_work
date_of_day date primary key
lead(date_of_day,10) Over (Order By date_of_day) lead_date
where date_of_day >= '01-mar-2004'
Where date_of_day = '01-mar-2004'
So this would find the date in the table that is 10 rows after the 01-mar-2004, when ordered by date
6 years ago I had the same requirement from one of the State Govt Depts in US.
First I created table that stores the all non-working days, this includes Holidays, Saturdays and Sundays. User is allowed to add future holidays or to change Sat/Sun as working day.
For a given date and the number of working days I wrote a function that will return the future working day.
This is not very difficult.
But you said, "Consquently we can't just discount Saturdays and Sundays as non working days". This shows the requirement is not very clear.