set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [Rodney Vinyard].[function_TodayIsRelevant]

-- =============================================

-- Author: <Rodney Vinyard>

-- Create date: <1/31/07>

-- Description:

-- Does @userDate match up with @FrequencyCode & @FrequencyValue?

--

-- <for input (1) @userDate (cannot use getDate() here in Function

-- (2) @FrequencyCode

-- (3) @FrequencyValue, returns 1 - true or 0 - false>

-- =============================================

-- IF @FrequencyCode = 'Daily' , return 1 (True)

-- else IF @FrequencyCode = 'Weekly' , @FrequencyValue = 'Wednesday'

-- else IF @FrequencyCode = 'Monthly' , @FrequencyValue = '1'

-- else IF @FrequencyCode = 'Monthly' , @FrequencyValue = 'Last'

-----------------------------------------------------------------

/*

select [Rodney Vinyard].usf_gen_Tickler_TodayIsRelevant (getdate(), 'Monthly', 'Last')

select [Rodney Vinyard].usf_gen_Tickler_TodayIsRelevant (getdate(), 'Monthly', '1')

select [Rodney Vinyard].usf_gen_Tickler_TodayIsRelevant (getdate(), 'Monthly', '31')

select [Rodney Vinyard].usf_gen_Tickler_TodayIsRelevant (getdate(), 'Weekly', 'Thursday')

select [Rodney Vinyard].usf_gen_Tickler_TodayIsRelevant (getdate(), 'Weekly', 'Wednesday')

select [Rodney Vinyard].usf_gen_Tickler_TodayIsRelevant (getdate(), 'Daily', 'Daily')

*/

-- =============================================

(

-- Add the parameters for the function here

@userDate as datetime,

@FrequencyCode varchar(20),

@FrequencyValue varchar(20)

)

RETURNS bit

AS

BEGIN

declare @myReturn bit

-----------------------------------------------

--Daily?

-----------------------------------------------

IF @FrequencyCode = 'Daily'

begin

-----------------------------------------------

--yes, Daily, all done

-----------------------------------------------

set @myReturn = 1 -- return true

end

-----------------------------------------------

--Weekly?

-----------------------------------------------

else IF @FrequencyCode = 'Weekly'

begin

-----------------------------------------------

--yes, weekday NAME, MONTH NAME is today?

-----------------------------------------------

declare @todayDayOfWeekName varchar(20)

set @todayDayOfWeekName = DATENAME(WEEKDAY, @userDate )

------------------------------ ------------------------------

--Wednesday January

if @todayDayOfWeekName = @FrequencyValue

set @myReturn = 1 -- return true

else

set @myReturn = 0 -- return false

end

-----------------------------------------------

--must be Monthly

-----------------------------------------------

else

begin

-----------------------------------------------

--which MONTH DAY NUMBER is today?

-----------------------------------------------

declare @TodaysMonthNumberString varchar(20)

SELECT @TodaysMonthNumberString = DATEPART(dd, @userDate);

-----------------------------------------------

--caller want's last day?

-----------------------------------------------

if @FrequencyValue = 'Last'

begin

-----------------------------------------------

--yes, caller want's last day

-----------------------------------------------

declare @endOfTodaysLastMonthNumberString varchar(20)

SELECT @endOfTodaysLastMonthNumberString =

(CASE MONTH(@userDate)

WHEN 1 THEN 31

WHEN 2 THEN (CASE YEAR(@userDate)%4 WHEN 0 THEN 29 ELSE 28 END)

WHEN 3 THEN 31

WHEN 4 THEN 30

WHEN 5 THEN 31

WHEN 6 THEN 30

WHEN 7 THEN 31

WHEN 8 THEN 31

WHEN 9 THEN 30

WHEN 10 THEN 31

WHEN 11 THEN 30

WHEN 12 THEN 31

END)

if @endOfTodaysLastMonthNumberString = @TodaysMonthNumberString

set @myReturn = 1 -- return true

else

set @myReturn = 0 -- return false

end

else

begin

-----------------------------------------------

--no, caller specifies day

-----------------------------------------------

if @FrequencyValue = @TodaysMonthNumberString

set @myReturn = 1 -- return true

else

set @myReturn = 0 -- return false

end

end

return @myReturn

END

go