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
(
DateIDint Primary Key
-- Generally the format yyyymmdd, e.g. 20081201 for 1 Dec 2008
,DateStampDateTime
,Year_IDint
,Month_Namevarchar(200)
,Month_IDint
,Day_IDint
,IsWeekDaybit
,IsHolidaybit
)
go
-- 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).
Cheers.
Zanoni Labuschagne
-- Function Code
creaet function fn_CalcDate
(
@StartDatedatetime
,@intDaysint
)
Returns DateTime
as
begin
Declare@ReturnDatedatetime
,@intRowsint
declare@DateRangetable
(
RowIDint identity(1,1)
,DateStampdatetime
)
Insert into @DateRange
(
DateStamp
)
SelectDateStamp
fromDimDate
whereisWeekday= 1
andisHoliday= 0
Order byDateStamp
Select@intRows= max(RowID)
from@DateRange
if @intDays <= @intRows
-- if the # of days in the table var is more than the days used to calc, find the relevant row
begin
Select@ReturnDate= DateStamp
from@DateRange
Where@intRows= RowID
end
else
-- if the days in the table is less, use the last
begin
Select@ReturnDate= max(DateStamp)
from@DateRange
end
Return@ReturnDate
end