UFN_DESIGNATION_GETTOTALDONORS

            CREATE function dbo.UFN_DESIGNATION_GETTOTALDONORS
            (
                @DESIGNATIONID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @ROLLUPTOTAL bit = 0
            ) 
            returns int
            with execute as caller
            as begin
                declare @RESULT int;

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

                if @ROLLUPTOTAL = 0
                    select @RESULT = count(distinct R.CONSTITUENTID)
                    from dbo.REVENUE R                     
                    inner join dbo.REVENUESPLIT RDS on R.ID = RDS.REVENUEID
                    where
                        (R.DATE >= @STARTDATE or @STARTDATE is null) and
                        (R.DATE <= @ENDDATE or @ENDDATE is null) and
                        RDS.DESIGNATIONID = @DESIGNATIONID and
                        (R.TRANSACTIONTYPECODE in (1,2,3,7) or
                         (R.TRANSACTIONTYPECODE = 0 and (RDS.APPLICATIONCODE = 0 or (RDS.APPLICATIONCODE = 1 and RDS.TYPECODE = 0))))
                else
                    begin
                    declare @DL1ID uniqueidentifier;
                    declare @DL2ID uniqueidentifier;
                    declare @DL3ID uniqueidentifier;
                    declare @DL4ID uniqueidentifier;
                    declare @DL5ID uniqueidentifier;

                    select @DL1ID = DESIGNATIONLEVEL1ID,
                            @DL2ID = DESIGNATIONLEVEL2ID,
                            @DL3ID = DESIGNATIONLEVEL3ID,
                            @DL4ID = DESIGNATIONLEVEL4ID,
                            @DL5ID = DESIGNATIONLEVEL5ID
                    from dbo.DESIGNATION
                    where ID = @DESIGNATIONID;

                    with DESIGNATIONS_CTE as (
                        select ID 
                        from dbo.DESIGNATION D
                        where D.DESIGNATIONLEVEL1ID = @DL1ID and
                              (D.DESIGNATIONLEVEL2ID = @DL2ID or @DL2ID is null) and 
                              (D.DESIGNATIONLEVEL3ID = @DL3ID or @DL3ID is null) and 
                              (D.DESIGNATIONLEVEL4ID = @DL4ID or @DL4ID is null) and 
                              (D.DESIGNATIONLEVEL5ID = @DL5ID or @DL5ID is null)
                        )

                    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
                            (R.TRANSACTIONTYPECODE in (1,3,7) or
                             (R.TRANSACTIONTYPECODE = 0 and (RDS.APPLICATIONCODE in (0, 3) or (RDS.APPLICATIONCODE = 1 and RDS.TYPECODE = 0))))
                            ), 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
                            R.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 (2, 7)
                        ), 0);                

                    end
                return @RESULT;
            end