fnIsSchduledForDate

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnIsSchduledForDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnIsSchduledForDate]
GO

CREATE FUNCTION [dbo].[fnIsSchduledForDate]
(
  @Date date,
  @enabled bit,
  @freq_type int,
@freq_interval int,
@freq_relative_interval int, 
@active_start_date int,
@active_end_date int
)
RETURNS bit
BEGIN
declare @result bit = 0

if @enabled = 0
begin
return @result
end

if @Date >= CAST(CAST(@active_start_date AS VARCHAR) AS DATE)
AND @Date <= CAST(CAST(@active_end_date AS VARCHAR) AS DATE)
BEGIN
if @freq_type = 4 --'Daily'
begin
set @result = 1
end
else if @freq_type = 8 --'Weekly'
begin
if ((DATEPART(dw, @Date) + @@DATEFIRST) % 7 = 1) and (@freq_interval & 1 = 1)   --Sunday
or ((DATEPART(dw, @Date) + @@DATEFIRST) % 7 = 2) and (@freq_interval & 2 = 2) --Monday
or ((DATEPART(dw, @Date) + @@DATEFIRST) % 7 = 3) and (@freq_interval & 4 = 4) --Tuesday
or ((DATEPART(dw, @Date) + @@DATEFIRST) % 7 = 4) and (@freq_interval & 8 = 8) --Wednesday
or ((DATEPART(dw, @Date) + @@DATEFIRST) % 7 = 5) and (@freq_interval & 16 = 16) --Thursday
or ((DATEPART(dw, @Date) + @@DATEFIRST) % 7 = 6) and (@freq_interval & 32 = 32) --Friday
or ((DATEPART(dw, @Date) + @@DATEFIRST) % 7 = 0) and (@freq_interval & 64 = 64) --Saturday
begin
set @result = 1
end
end
else if @freq_type = 16 --'Monthly'
begin
if DAY(@Date) = @freq_interval
begin
set @result = 1
end
end
else if @freq_type = 32 --'Monthly relative'
begin
/*freq_relative_interval:
WHEN  1 THEN 'First'
WHEN  2 THEN 'Second'
WHEN  4 THEN 'Third'
WHEN  8 THEN 'Fourth'
WHEN 16 THEN 'Last'
  */
-- 1 Sunday, 2 Monday, 3 Tuesday, 4 Wednesday, 5 Thursday, 6 Friday, 7 Saturday
-- 9: Weekday, 10: Weekend
if @freq_interval >= 1 and @freq_interval <= 7 or @freq_interval in (9, 10)
begin
if @freq_interval >= 1 AND @freq_interval <= 7 AND (DATEPART(dw, @Date) + @@DATEFIRST) % 7 = @freq_interval % 7
OR @freq_interval = 9 AND (DATEPART(dw, @Date) + @@DATEFIRST) % 7 in (2, 3, 4, 5, 6)
OR @freq_interval = 10 and (DATEPART(dw, @Date) + @@DATEFIRST) % 7 in (0, 1)
begin
declare @weekth int
select @weekth = (DAY(@Date) + 6) / 7
if (@freq_relative_interval = 1 and @weekth = 1)
or (@freq_relative_interval = 2 and @weekth = 2)
or (@freq_relative_interval = 4 and @weekth = 3)
or (@freq_relative_interval = 8 and @weekth = 4)
or (@freq_relative_interval = 16 and MONTH(@DATE) <> MONTH(DATEADD(dd, 7, @DATE)))
begin
set @result = 1
end
end
end
--8: Day
else if @freq_interval = 8
begin
if (@freq_relative_interval = 1 and DAY(@Date) = 1)
or (@freq_relative_interval = 2 and DAY(@Date) = 2)
or (@freq_relative_interval = 4 and DAY(@Date) = 3)
or (@freq_relative_interval = 8 and DAY(@Date) = 4)
or (@freq_relative_interval = 16 and MONTH(@DATE) <> MONTH(DATEADD(dd, 1, @DATE)))
begin
set @result = 1
end
end
end
END

  return @result
END
GO

--week relative
--select dbo.[fnIsSchduledForDate]( 
-- '2018-02-01', --@date
--  0, --@enabled 
-- 32, --@freq_type
-- 5, --@freq_interval
-- 1, --@freq_relative_interval
-- 20170101, --@active_start_date
-- 20190101 ) --@active_end_date

select dbo.[fnIsSchduledForDate]( 
'2018-02-28', --@date
  1, --@enabled 
32, --@freq_type
4, --@freq_interval
8, --@freq_relative_interval
20170101, --@active_start_date
20190101 ) --@active_end_date