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 ignores weekends and holidays. This can easily be altered for countries where weekends are on other days of the week.
(The earlier version of this function would not work for holidays recorded as a series of contiguous dates. - This version does.).