TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Kim Snell
NA
2
615
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:
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
--Get Weekdays
IF OBJECT_ID(
'GetWeekdays'
)
IS
NOT
NULL
DROP
FUNCTION
GetWeekdays
Go
Create
FUNCTION
GetWeekdays
(
-- Add the parameters for the function here
@startDate
date
,
@endDate
date
--@Holidays HolidayType READONLY
)
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
--Need a variable for the number of holidays that would equal the number of holidays
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
END
Go
DECLARE
@WeekdaysToDate
INT
Select
@WeekdaysToDate = dbo.GetWeekdays(
CAST
(DATEADD(
month
, DATEDIFF(
month
, 0, GETDATE()), 0)
AS
DATE
),getdate()-1)
Reply
Answers (
1
)
Need Help in Query ?
How optimize query?