Kim Snell

Kim Snell

  • NA
  • 2
  • 547

Passing Table Type Variables to User Defined Functions

Jan 17 2019 1:03 PM
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:
  1. DECLARE @Holidays HolidayType  
  2. INSERT INTO @Holidays(Holiday) VALUES('New Years Day','Memorial Day','Fourth of July','Labor Day','Thanksgiving Day','Christmas Day');  
  3. INSERT INTO @Holidays(HolidayDate) VALUES('1/1/2019','5/27/2019''7/4/2019','9/2/2019''11/28/2019''12/25/2019');  
  4. Select * from @Holidays  
  5. --Get Weekdays  
  6. IF OBJECT_ID('GetWeekdays'IS NOT NULL  
  7. DROP FUNCTION GetWeekdays  
  8. Go  
  9. Create FUNCTION GetWeekdays  
  10. (  
  11. -- Add the parameters for the function here  
  12. @startDate date,  
  13. @endDate date  
  14. --@Holidays HolidayType READONLY  
  15. )  
  16. RETURNS int  
  17. AS  
  18. BEGIN  
  19. declare @full_weeks int,@remaining_days int,@work_days_in_full_weeks int,@startingWeekday int, @work_days_in_partial_week int,@HolidayNumber HolidayType READONLY  
  20. set @full_weeks = (datediff(d,@startDate,@endDate)+1)/7  
  21. set @remaining_days = (datediff(d,@startDate,@endDate)+1)%7  
  22. set @work_days_in_full_weeks = @full_weeks*5  
  23. set @startingWeekday = datepart(dw,@startDate)  
  24. 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  
  25. set @work_days_in_partial_week=case when @work_days_in_partial_week<0 then 0 else @work_days_in_partial_week end  
  26. --Need a variable for the number of holidays that would equal the number of holidays  
  27. return @work_days_in_full_weeks+@work_days_in_partial_week --This needs to subtract the number of holidays from the variable in the previous line  
  28. END  
  29. Go  
  30. DECLARE @WeekdaysToDate INT  
  31. Select @WeekdaysToDate = dbo.GetWeekdays(CAST(DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)AS DATE),getdate()-1)  

Answers (1)