Very often there is a need to generate a date by adding (or subtracting) a number of working days. A process might have a lead time of N days and so a date which is N working days after another is required. Or perhaps an activity has to happen on a given date and a pre-activity date which is M working days before that has to be generated.
Straightforward enough if only weekends have to be considered - but what happens when statutory holidays or other non-working days get in the way?
The following Scalar Valued Function allows the user to generate days which are a number of working days either before or after a given date. It uses a table called tblHoliday in which each record represents a single non-working day. The table has an ID field called HolidayID and the date of the non-working day is held in a field called HolidayDate.
The Function will work however day-of-the-week-numbering has been set up since it finds the DW (day of the Week) values for Saturday and Sunday before performing a simple step-through-and-count loop which hops over weekends and holidays. This can easily be altered for countries where weekends are on other days of the week.