UFN_DESIGNATIONLEVEL_GETTOTALDONORS

            CREATE function dbo.UFN_DESIGNATIONLEVEL_GETTOTALDONORS
            (
                @DESIGNATIONLEVELID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null
            ) 
            returns int
            with execute as caller
            as begin
                declare @RESULT int;

                select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                        @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                with DESIGNATIONS_CTE as (
                    select ID 
                    from dbo.DESIGNATION D
                    where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID or 
                          D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID
                    )

                select @RESULT = 
                    coalesce((select count(distinct R.CONSTITUENTID)
                    from dbo.REVENUESPLIT RDS
                    inner join DESIGNATIONS_CTE D on RDS.DESIGNATIONID = D.ID
                    inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                    where
                        (R.DATE >= @STARTDATE or @STARTDATE is null) and
                        (R.DATE <= @ENDDATE or @ENDDATE is null) and
                        dbo.UFN_REVENUE_HASDESIGNATION(R.TRANSACTIONTYPECODE, RDS.APPLICATIONCODE) = 1
                    ), 0)
                    +
                        --payments of pledges not included in date range                        

                    coalesce((select count(distinct R.CONSTITUENTID)
                    from dbo.REVENUESPLIT RDS
                    inner join DESIGNATIONS_CTE D on RDS.DESIGNATIONID = D.ID
                    inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                    where
                        RDS.ID NOT IN
                            (SELECT IP.PAYMENTID
                                FROM DBO.INSTALLMENTPAYMENT IP
                                inner join dbo.REVENUE PLEDGE on IP.PLEDGEID = PLEDGE.ID
                                inner join dbo.REVENUESPLIT PLEDGESPLIT on PLEDGE.ID = PLEDGESPLIT.REVENUEID
                                inner join DESIGNATIONS_CTE PD on PLEDGESPLIT.DESIGNATIONID = PD.ID
                                WHERE (PLEDGE.DATE >= @STARTDATE or @STARTDATE is null) and
                                    (PLEDGE.DATE <= @ENDDATE or @ENDDATE is null))
                         and
                        (R.DATE >= @STARTDATE or @STARTDATE is null) and
                        (R.DATE <= @ENDDATE or @ENDDATE is null) and
                            (R.TRANSACTIONTYPECODE = 0) and 
                            (RDS.APPLICATIONCODE in (1,3,7))), 0);


                return @RESULT;
            end