UFN_VOLUNTEER_DAYOFWEEKMATCHES_VALID

CREATE function dbo.UFN_VOLUNTEER_DAYOFWEEKMATCHES_VALID
(
    @SINGLEDAY datetime, 
    @OCCURRENCETYPE tinyint, 
    @STARTDATETIME datetime, 
    @STARTMONTHDAY dbo.UDT_MONTHDAY, 
    @ENDDATETIME datetime, 
    @ENDMONTHDAY dbo.UDT_MONTHDAY,
    @DAYOFWEEKCODE tinyint
)
returns bit
with execute as caller
as begin

    declare @MONTH as int;
    declare @DAY as int;
    declare @VALID as bit = 0

    if @SINGLEDAY is null
        return 0;
    else
    begin
        if @OCCURRENCETYPE = 2 --ongoing

            set @VALID = 1;

        if @OCCURRENCETYPE = 0 --onetime

            if @SINGLEDAY between @STARTDATETIME and @ENDDATETIME
                set @VALID = 1;

        if @OCCURRENCETYPE = 1 --recurring

        begin
            set @MONTH = month(@SINGLEDAY);
            set @DAY = day(@SINGLEDAY);

            if @STARTMONTHDAY <= @ENDMONTHDAY
                begin
                    if (@MONTH > left(@STARTMONTHDAY,2) 
                        or (@MONTH = left(@STARTMONTHDAY,2) and @DAY>= right(@STARTMONTHDAY,2)))
                        and
                        (@MONTH < left(@ENDMONTHDAY,2) 
                            or (@MONTH = left(@ENDMONTHDAY,2) and @DAY<= right(@ENDMONTHDAY,2)))  

                        set @VALID = 1
                end
            else
                begin
                    if (@MONTH > left(@STARTMONTHDAY,2)
                        or (@MONTH = left(@STARTMONTHDAY,2) and @DAY>= right(@STARTMONTHDAY,2)))
                        or (@MONTH < left(@ENDMONTHDAY,2)
                            or (@MONTH = left(@ENDMONTHDAY,2) and @DAY<= right(@ENDMONTHDAY,2)))  

                        set @VALID = 1
                end
        end

        -- Done with first set of checks... now check day of week matches.

        if @VALID = 0
            return 0;

        declare @DATEDAYOFWEEK tinyint;

        if @DAYOFWEEKCODE = 0 --everyday

            return 1;

        set @DATEDAYOFWEEK = datepart(dw, @SINGLEDAY);

        if (@DAYOFWEEKCODE >= 1 and @DAYOFWEEKCODE <= 7) --actual day

            if (@DATEDAYOFWEEK = @DAYOFWEEKCODE)
                return 1;
            else 
                return 0;

        if @DAYOFWEEKCODE = 8 -- weekends

            if (@DATEDAYOFWEEK = 1 or @DATEDAYOFWEEK = 7)
                return 1;
            else 
                return 0;

        if @DAYOFWEEKCODE = 9 -- weekdays

            if (@DATEDAYOFWEEK >= 2 and @DATEDAYOFWEEK <= 6)
                return 1;
            else 
                return 0;
    end                            

    return 0;
end