UDF table lookup to override returned value of function
-
Hi,
I'm trying to modify a UDF that calculates whether or not a datetime value falls on a business day, or a weekend day/holiday. It was suggested by the original author that I could create an additional lookup within the UDF so that it overrides my WorkDayType value by checking a holiday table.
Here's the code:
CREATE FUNCTION [dbo].[CORE_udfBusinessDayTime]
(
@dt DateTime,
@TimeType tinyint -- @timetype 0 = start, 1 = end
)
RETURNS DateTime
AS
BEGIN
DECLARE
@BOD DateTime,
@DayOfWeek int,
@WorkDayType tinyint
-- day of week is 1-7, Sunday=1 (by default,
-- unless SET DATEFIRST has been called)
SET @DayOfWeek = DATEPART( dw, @dt )
-- get the type of workday:
-- 0=regular full business day, 6:30AM to 3:30PM
-- 1=short business day (Sat. or short working day), 7AM-11AM
-- 2=not a business day (Sun. or holiday)
SET @WorkDayType=
CASE @DayOfWeek
WHEN 1 THEN 2
WHEN 7 THEN 1
ELSE 0
END
-- TODO: add support for holidays by doing an additional
-- lookup on @dt, getting an overrridden WorkDayType from
-- table for special days
IF @WorkDayType=2
RETURN NULL
-- get beginning of day
SET @BOD=CONVERT(CHAR(10), @dt, 101)
-- TODO: put times in table
RETURN
CASE @TimeType
WHEN 0 THEN -- start of workday
CASE @WorkDayType
WHEN 1 THEN DATEADD( hh, 7, @BOD )
ELSE DATEADD( mi, 30, DATEADD( hh, 6, @BOD ))
END
ELSE -- end of workday
CASE @WorkDayType
WHEN 1 THEN DATEADD( hh, 11, @BOD )
ELSE DATEADD( mi, 30, DATEADD( hh, 15, @BOD ))
END
END
END
I thought it might be similar to how the times could be stored in a table, and I've been experimenting by creating a "holidays" table, as suggested... but am not sure how to add a subquery to override the WorkDayType based upon the values of that table.
Can anyone suggest how I can do this?
-
Try something like this:
create table holiday(holiday datetime primary key)
insert holiday values ('01 Jan 2006')
insert holiday values ('25 Dec 2006')
go
--in your function add
if exists(select * from holiday where holiday = DATEADD(DD, 0, DATEDIFF(DD, 0, @dt)))
set @WorkDayType = 2
-
That worked perfectly! Thank you!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply