TSQL Function to calculate 30 WORKING days Date from a Specified Date (SQL Server 2005)

Question

TSQL Function to calculate 30 WORKING days Date from a Specified Date (SQL Server 2005)?

Input parameters would be Date and Number of Working Days.
Output would be the Calculated Date.

This would exclude Saturday, Sunday, Holidays and Day Holiday was observered.
i.e. If the Holiday falls on a weekend but it is observed on the Friday or Monday after the holiday.

For the Holidays we have a table with holiday and day it is being observed date.

Answer 1

There's one right in the SQL online help if you scroll down to UDF to return the number of business days, including a check to a bank holidays table you can tweak this.

Answer 2

Instead of writing a tsql function, it might easier if you build a table that's similar to the Date Dimension (DimDate) table in data warehouse. DimDate would contain a column named isHoliday. You can also add other columns that might be useful. Then you write a script to populate DimDate

Then you can run a query off it.

Answer 3

Have a look at this article by Celko - in general you need to "pre-calculate" a calendar table to take in account all possible vagaries like Easter, bank holidays etc.

Answer 4

I don't have a table of holidays handy, so I haven't tested this very much - but as nobody else has attempted an answer, here's how I'd start:

declare @tempDate datetime,
    @StartDate datetime,
    @WorkingDays int,
    @NonWorkingDays int,
    @TargetDate datetime

set @StartDate = '2010-10-26' --Change this to a paramter
set @WorkingDays = 9 --Change this to a parameter   
set @NonWorkingDays = -1

/*Work out the answer ignoring holidays */
set @tempDate = dateadd(d,@WorkingDays,@StartDate)

while (dateadd(d,@WorkingDays + @NonWorkingDays, @StartDate) < @tempDate)
begin
    /*Work out how many holidays are in the interval we've worked out*/
    select @NonWorkingDays = count(HolidayDate)
    from Holidays
    where HolidayDate between @StartDate and @tempDate;

    /*Extend the interval to include the holidays we've just found*/
    set @tempDate = dateadd(d,@NonWorkingDays,@tempDate)

    /*See if @NonWorkingDays has changed with the new @tempDate*/
    select @NonWorkingDays = count(HolidayDate)
    from Holidays
    where HolidayDate between @StartDate and @tempDate;

end

set @TargetDate = dateadd(d,@WorkingDays + @NonWorkingDays, @StartDate)

print 'Target Date: ' + cast(@TargetDate as varchar(50))

Note this only works for Holidays at the moment - not weekends. You'd have to load all weekends into the holiday table (or join to a weekends table or use the DATENAME function) but the calculation should be the same.

Not sure how your Holiday table handles duplicate dates (eg. Boxing Day and St Stephen's Day both fall on 26th Dec) so you might need to take account of that.