UFN_REVENUE_GETPREVIOUSTRANSACTIONDATE_BYID_1_1

            CREATE function dbo.UFN_REVENUE_GETPREVIOUSTRANSACTIONDATE_BYID_1_1
            (
                @REVENUEID uniqueidentifier,
                @ASOFDATE datetime
            )
            returns datetime
            as
            begin
                declare @STARTDATE datetime;
                declare @FREQUENCYCODE tinyint;
                declare @NEXTTRANSACTIONDATE datetime;
                declare @ISLASTDAYOFMONTH bit;

                select
                    @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(coalesce(SCHEDULESEEDDATE, STARTDATE)),
                    @FREQUENCYCODE = FREQUENCYCODE,
                    @NEXTTRANSACTIONDATE = dbo.UFN_DATE_GETEARLIESTTIME(coalesce(@ASOFDATE, NEXTTRANSACTIONDATE))
                from dbo.REVENUESCHEDULE
                where ID = @REVENUEID;

                declare @DAYSINSTARTMONTH tinyint;
                declare @STARTMONTH tinyint;
                declare @STARTYEAR smallint;
                declare @STARTDAY tinyint;

                select
                    @STARTMONTH = datepart(month, @STARTDATE),
                    @STARTYEAR = datepart(year, @STARTDATE),
                    @STARTDAY = datepart(day, @STARTDATE)

                set @DAYSINSTARTMONTH = dbo.UFN_DAYSINMONTH(@STARTMONTH, @STARTYEAR)
                select 
                    @ISLASTDAYOFMONTH = case when @STARTDAY >= @DAYSINSTARTMONTH then 1 else 0 end;

                declare @POTENTIALDATE datetime;
                set @POTENTIALDATE = @NEXTTRANSACTIONDATE;

                while @POTENTIALDATE >= @NEXTTRANSACTIONDATE
                begin
                    if @FREQUENCYCODE = 0 --Annually

                        set @POTENTIALDATE = dateadd(year, -1, @POTENTIALDATE);
                    else if @FREQUENCYCODE = 1 --Semi-annually

                        set @POTENTIALDATE = dateadd(month, -6, @POTENTIALDATE);
                    else if @FREQUENCYCODE = 2 --Quarterly

                        set @POTENTIALDATE = dateadd(quarter, -1, @POTENTIALDATE);
                    else if @FREQUENCYCODE = 3 --Monthly

                    begin
                        set @POTENTIALDATE = dateadd(month, -1, @POTENTIALDATE);
                    end
                    else if @FREQUENCYCODE = 6 --Bimonthly

                        set @POTENTIALDATE = dateadd(month, -2, @POTENTIALDATE);        
                    else if @FREQUENCYCODE = 7 --Semi-monthly

                    begin
                        if datepart(day, @POTENTIALDATE) > 1 and datepart(day, @POTENTIALDATE) <= 15
                            set @POTENTIALDATE = dateadd(day, -1 * (datepart(day, @POTENTIALDATE) - 1), @POTENTIALDATE);
                        else
                        begin
                            if datepart(day, @POTENTIALDATE) = 1
                                set @POTENTIALDATE = dateadd(m, -1, @POTENTIALDATE);

                            set @POTENTIALDATE = dateadd(day, (-1 * (datepart(day, @POTENTIALDATE) - 15)), @POTENTIALDATE);
                        end             
                    end
                    else if @FREQUENCYCODE = 8 --Biweekly

                        set @POTENTIALDATE = dateadd(day, -14, @POTENTIALDATE);
                    else if @FREQUENCYCODE = 9 --Weekly

                        set @POTENTIALDATE = dateadd(week, -1, @POTENTIALDATE);
                    else if @FREQUENCYCODE = 10 --Every4Weeks

                        set @POTENTIALDATE = dateadd(day, 28, @POTENTIALDATE);
                    else
                        set @POTENTIALDATE = dateadd(day, -1, @NEXTTRANSACTIONDATE); --Don't know how to handle the frequency. Just use the day before @NEXTTRANSACTIONDATE so it'll break out of the loop

                end

                --If the start date is the last day of the month and the frequency is monthly,

                --make sure that the next transaction date is the last day of the month

                if @FREQUENCYCODE = 3
                begin
                    declare @DAYSINPOTENTIALMONTH tinyint
                    declare @POTENTIALDAY tinyint;

                    set @DAYSINPOTENTIALMONTH = dbo.UFN_DAYSINMONTH(datepart(month, @POTENTIALDATE), datepart(year, @POTENTIALDATE));
                    set @POTENTIALDAY = datepart(day, @POTENTIALDATE);

                    if @ISLASTDAYOFMONTH = 1 and @POTENTIALDAY <> @DAYSINPOTENTIALMONTH
                        set @POTENTIALDATE = dateadd(day, (@DAYSINPOTENTIALMONTH - @POTENTIALDAY), @POTENTIALDATE)

                    if @ISLASTDAYOFMONTH = 0 and @STARTDAY > @POTENTIALDAY and @STARTDAY <= @DAYSINPOTENTIALMONTH
                        set @POTENTIALDATE = dateadd(day, (@STARTDAY - @POTENTIALDAY), @POTENTIALDATE)

                end

                return @POTENTIALDATE;
            end