Hi, J Gravelle
There is a data warehousing structure that will make it much easier to do this calc, as well as the flexibility to do loads more...The Date dimension
By using a date dimension, you won't need to loop, and can use one set-based statement to get the total.
Just some info on a date dimension - essentially, a date dimension is a table that allows you to set certain metadata around a date. You can for instance indicate whether a date is a week day, holiday, etc.
An example of a date dimension is:
Create table DimDate
DateID int Primary Key
-- Generally the format yyyymmdd, e.g. 20081201 for 1 Dec 2008
-- Populate the table using a MDM / SQL tool / script
Then populate the table with all the dates between a specific range e.g. 1 Jan 2000 to 31 Dec 2020
You can now at a glance see if a date is a holiday or a work day.
Now, if you want to calc the date of "x" working days forward, you can write a simple function like the one below (there are probably LOADS of better ways than what I did below, but this was the first way that came to mind).
-- Function Code
creaet function fn_CalcDate
Declare @ReturnDate datetime
declare @DateRange table
RowID int identity(1,1)
Insert into @DateRange
where isWeekday = 1
and isHoliday = 0
Order by DateStamp
Select @intRows = max(RowID)
if @intDays <= @intRows
-- if the # of days in the table var is more than the days used to calc, find the relevant row
Select @ReturnDate = DateStamp
Where @intRows = RowID
-- if the days in the table is less, use the last
Select @ReturnDate = max(DateStamp)
Select 'Remember, we were all newbies once'
Order by Experience