Very Computer

I want to add a number of normal working days to a date
taking account of weekends and holidays.

For example, I want to add 2 days to the following date,
but to exclude the weekend:

Friday 4th October. - the result should be Tuesday 8th
October

I have table of holidays, I want the calculation to take
account of any holidays as well. Using the same example as
above:

If Monday 7th October was a holiday then the date returned
should be Wednesday 9th October

If Tuesday 8th October was a holiday (but Monday isn't)
then the date returned should be Thursday 10th October

A further twist to this problem is that I want to take
account of normal working hours as well. For example, I
may want to add 2 normal working hours to my date. If a
normal working day is 08:00 to 17:00 and Monday 7th
October is a holiday, then adding 2 hours to Friday 4th
October at 16:00 should return Tuesday 8th October 09:00

How can this calculation be expressed in SQL?

I would appreciate any assistance
Justin

For requirements of this nature it is always better to have
a calendar table. And you can use this calendar table in your
JOINS to calculate the dates based on the IsWorkingDay COLUMN.
You can easily create one using something along the lines of:

SELECT TOP 100 PERCENT
       DateVal,
       DayVal,
       NonBusinessDay,
       CASE WHEN NonBusinessDay IS NULL
            AND DayVal NOT IN ('Saturday', 'Sunday') THEN 1
       ELSE 0 END AS [IsWorkingDay]
  FROM
      (
      SELECT DATEADD(d, Dates.Digit, '1-1-2002'),
             DATENAME(dw, DATEADD(d, Dates.Digit, '1-1-2002')),
             HolidayTable.Holidays --  another table if required holding the
                                   -- national holidays/non business days
        FROM (
             SELECT 1 * Unit.Digit + 10 * Ten.Digit + 100 * Hundred.Digit
               FROM (
                    SELECT 0 UNION  SELECT 1 UNION  SELECT 2 UNION
                    SELECT 3 UNION  SELECT 4 UNION  SELECT 5 UNION
                    SELECT 6 UNION  SELECT 7 UNION  SELECT 8 UNION
                    SELECT 9
                  ) AS Unit( Digit )
                  CROSS JOIN (
                    SELECT 0 UNION  SELECT 1 UNION  SELECT 2 UNION
                    SELECT 3 UNION  SELECT 4 UNION  SELECT 5 UNION
                    SELECT 6 UNION  SELECT 7 UNION  SELECT 8 UNION
                    SELECT 9
                  ) AS Ten( Digit )
                  CROSS JOIN (
                    SELECT 0 UNION  SELECT 1 UNION  SELECT 2 UNION
                    SELECT 3
              ) AS Hundred( Digit )
              ) AS Dates( Digit ) LEFT OUTER JOIN tblNonBusDates
       ON DATEADD(d, Dates.Digit, '1-1-2002') = tblNonBusDates.NonBusDate
     WHERE Dates.Digit BETWEEN 0 And 364) _d1(DateVal, DayVal,
NonBusinessDay)
 ORDER BY DateVal

--
- Anith