I have a function that returns number of workdays between 2 dates. I need it to exclude a set of holidays from that count. I created a table type variable to pass to my function, but cannot figure out how to get a number of holidays in the table type between the two dates.
My code:
- DECLARE @Holidays HolidayType
- INSERT INTO @Holidays(Holiday) VALUES('New Years Day','Memorial Day','Fourth of July','Labor Day','Thanksgiving Day','Christmas Day');
- INSERT INTO @Holidays(HolidayDate) VALUES('1/1/2019','5/27/2019', '7/4/2019','9/2/2019', '11/28/2019', '12/25/2019');
- Select * from @Holidays
-
- IF OBJECT_ID('GetWeekdays') IS NOT NULL
- DROP FUNCTION GetWeekdays
- Go
- Create FUNCTION GetWeekdays
- (
-
- @startDate date,
- @endDate date
-
- )
- RETURNS int
- AS
- BEGIN
- declare @full_weeks int,@remaining_days int,@work_days_in_full_weeks int,@startingWeekday int, @work_days_in_partial_week int,@HolidayNumber HolidayType READONLY
- set @full_weeks = (datediff(d,@startDate,@endDate)+1)/7
- set @remaining_days = (datediff(d,@startDate,@endDate)+1)%7
- set @work_days_in_full_weeks = @full_weeks*5
- set @startingWeekday = datepart(dw,@startDate)
- set @work_days_in_partial_week = @remaining_days - case when @startingWeekday = 1 then 1 when @startingWeekday = 7 then 2 when @remaining_days+@startingWeekday-1=7 then 1 when @remaining_days+@startingWeekday-1>=8 then 2 else 0 end
- set @work_days_in_partial_week=case when @work_days_in_partial_week<0 then 0 else @work_days_in_partial_week end
-
- return @work_days_in_full_weeks+@work_days_in_partial_week
- END
- Go
- DECLARE @WeekdaysToDate INT
- Select @WeekdaysToDate = dbo.GetWeekdays(CAST(DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)AS DATE),getdate()-1)