Report DATE, X Working Days from Input Date

Hi,

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.

- Tony.

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 ...

Code:

---

create table date_work
        (
        date_of_day        date primary key
        )
/


Select lead_date
From
  (
  Select date_of_day,
            lead(date_of_day,10) Over (Order By date_of_day) lead_date
  From  date_work
  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.

Tamil

Thanks very much for the responses.

The users further complicated the issue by stating that they wanted the function to also give an offset date (negative) to the input date.

What I've done is (as per Slimdave) [many thanks], simplified the issue be populating a table full of working days.

I've also included a column of 'day_sequence', and ordered this from min date to max date.

Now, the function will derive the day_sequence for a given input date, add the (+ve or -ve) offset number of days, and then re-lookup the given working day for the sequence.

The quickest solution I thought up on the spur of the moment, but obviously involves a couple of table scans (albeit very small table).

Anyone an idea on refining my current end product?

:-)

T.