CREATE FUNCTION [_SMDBA_].[IsWorkTime] 
(
	-- Add the parameters for the function here
	@WorkScheduleName nvarchar(60),
	@DateValue datetime
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result int

	-- Add the T-SQL statements to compute the return value here
	-- The first thing we do is check if the given date falls on a holiday
	IF (SELECT COUNT(*) 
				FROM	dbo.HOLIDAYLIVE HL
				JOIN	dbo.HOLIDAYSETDETAILS HSD
					ON	HSD.[HOLIDAYDEF] = HL.[HOLIDAYDEF]
				JOIN	dbo.HOLIDAYSET HS
					ON	HSD.[HOLIDAYSETDEF] = HS.[SEQUENCE]
				WHERE	NAME = @WorkScheduleName
				AND		WORKDATE = CAST(CONVERT(nvarchar(50), @DateValue, 102) As DateTime)) > 0
	BEGIN
		SET @Result = 0
		RETURN @Result
	END

	-- We work out how many seconds have passed since midnight
	DECLARE	@SecondsPassed int
	SELECT	@SecondsPassed = DATEDIFF(ss, CAST(CONVERT(nvarchar(50), @DateValue, 102) As DateTime), @DateValue)

	-- Now we need the number of half-hours that number of seconds equates to
	DECLARE @HalfHours int
	SELECT	@HalfHours = (@SecondsPassed / 1800) + 1

	-- We need to know if there is an X at the same character as the no of half hours for the given day
	DECLARE @Mask nchar(48)
	SELECT	@Mask = CASE
		WHEN DATEPART(dw, @DateValue) = 1 THEN (SELECT SUN FROM dbo.HOLIDAYSET WHERE NAME = @WorkScheduleName)
		WHEN DATEPART(dw, @DateValue) = 2 THEN (SELECT MON FROM dbo.HOLIDAYSET WHERE NAME = @WorkScheduleName)
		WHEN DATEPART(dw, @DateValue) = 3 THEN (SELECT TUE FROM dbo.HOLIDAYSET WHERE NAME = @WorkScheduleName)
		WHEN DATEPART(dw, @DateValue) = 4 THEN (SELECT WED FROM dbo.HOLIDAYSET WHERE NAME = @WorkScheduleName)
		WHEN DATEPART(dw, @DateValue) = 5 THEN (SELECT THU FROM dbo.HOLIDAYSET WHERE NAME = @WorkScheduleName)
		WHEN DATEPART(dw, @DateValue) = 6 THEN (SELECT FRI FROM dbo.HOLIDAYSET WHERE NAME = @WorkScheduleName)
		WHEN DATEPART(dw, @DateValue) = 7 THEN (SELECT SAT FROM dbo.HOLIDAYSET WHERE NAME = @WorkScheduleName)
	END

	SELECT	@Result = CASE
		WHEN SUBSTRING(@Mask, @HalfHours, 1) = 'X' THEN 1
		ELSE 0
	END
	
	-- Return the result of the function
	RETURN @Result

END