UFN_RECURRINGGIFT_GETPASTDUEINSTALLMENTCOUNT

Return

Return Type
int
Parameters

Parameter	Parameter Type	Mode
@REVENUEID	uniqueidentifier	IN
@ASOFDATE	datetime	IN
@IGNORESTATUS	bit	IN
Definition

Copy


            create function dbo.UFN_RECURRINGGIFT_GETPASTDUEINSTALLMENTCOUNT(
                @REVENUEID uniqueidentifier, 
                @ASOFDATE datetime,
                @IGNORESTATUS  bit = 0
            ) 
            returns int
            begin

                declare @RETVAL int = 0;

                declare @ENDDATE datetime=dbo.UFN_DATE_GETEARLIESTTIME(
                    case
                        when @ASOFDATE < getdate()
                            then @ASOFDATE
                        else getdate()
                    end
                );

                select @RETVAL = count(ID) 
                from RECURRINGGIFTINSTALLMENT
                where REVENUEID=@REVENUEID
                    and DATE <= @ENDDATE and (@IGNORESTATUS = 1 or STATUSCODE = 1)
                    and dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(ID) = TRANSACTIONAMOUNT;

                if @IGNORESTATUS = 1
                    select @RETVAL = @RETVAL + count(ID)
                    from REVENUE R
                    cross apply dbo.UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS(R.ID) I
                    where R.ID = @REVENUEID
                    and I.DATE <= @ENDDATE;

                return @RETVAL;
            end