UFN_REVENUE_GETMISSEDTRANSACTIONCOUNT

Returns the number of consecutive missed transaction between the as of date and the next transaction date.

Return

Return Type
int

Parameters

Parameter	Parameter Type	Mode	Description
@REVENUEID	uniqueidentifier	IN
@ASOFDATE	datetime	IN

Definition

Copy



            CREATE function [dbo].[UFN_REVENUE_GETMISSEDTRANSACTIONCOUNT] (
                    @REVENUEID uniqueidentifier, 
                    @ASOFDATE datetime) 
                returns int

            begin
                declare @FREQUENCYCODE int
                declare @NEXTTRANSACTION datetime
                declare @ENDDATE datetime
                declare @RETVAL  int

                set @RETVAL = 0

                select 
                    @FREQUENCYCODE = FREQUENCYCODE,
                    @NEXTTRANSACTION = coalesce(NEXTTRANSACTIONDATE, @ASOFDATE),
                    @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(ENDDATE)
                from dbo.REVENUESCHEDULE
                where ID = @REVENUEID

                --JamesWill CR275720-052107 2007/05/23 Take end dates into account if the recurring gift has an end date set.

                while @NEXTTRANSACTION < @ASOFDATE and (@NEXTTRANSACTION <= @ENDDATE or @ENDDATE is null)
                begin
                    set @RETVAL = @RETVAL + 1    
                    set @NEXTTRANSACTION = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE_BYID_1_1(@REVENUEID, @NEXTTRANSACTION)
                end

                return @RETVAL
            end