Answered by:
Creating A function that skips certain days

Question
-
I Need to create a SQL function for the TEST database that takes two dates as parameters and returns the number of business days between them. This calculation will skip over weekends and Company Holidays. Holidays can be found in the Adviser database's CompanyHoliday table.
Answers
-
Assuming you have a Calendar table with all dates and IsWeekend column as one of the columns, then
select count(*) as NumberOfBusinessDates from dbo.Calendar c where
c.[Date] between @StartDate and @EndDate and c.IsWeekend = 0 and not exists (select 1 from dbo.Holidays h
where h.[Date] = c.[Date])
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
My TechNet articles- Marked as answer by KingRBG2009 Thursday, November 14, 2019 9:20 PM
All replies
-
Assuming you have a Calendar table with all dates and IsWeekend column as one of the columns, then
select count(*) as NumberOfBusinessDates from dbo.Calendar c where
c.[Date] between @StartDate and @EndDate and c.IsWeekend = 0 and not exists (select 1 from dbo.Holidays h
where h.[Date] = c.[Date])
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
My TechNet articles- Marked as answer by KingRBG2009 Thursday, November 14, 2019 9:20 PM
-
-
-
Please see "dbo.udf_AddBusinessDays" in:
https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/