UFN_MEMBER_APPEALREVENUECOUNTS

            CREATE function dbo.UFN_MEMBER_APPEALREVENUECOUNTS
            (@STARTDATE datetime, @ENDDATE datetime)
            returns @MEMBERSHIPREVENUEINFO table
            (
                APPEALID uniqueidentifier NOT NULL PRIMARY KEY CLUSTERED,
                DONORCOUNT int NOT NULL,
                GIFTCOUNT int NOT NULL
            )
            as
            begin

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


                insert into @MEMBERSHIPREVENUEINFO
                select
                    REVENUE_EXT.APPEALID,
                    count(distinct(r.CONSTITUENTID)),
                    count(distinct(r.ID))
                from dbo.FINANCIALTRANSACTION r
                inner join dbo.REVENUE_EXT on r.ID = REVENUE_EXT.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM on r.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                inner join dbo.APPEAL on REVENUE_EXT.APPEALID = APPEAL.ID
                inner join dbo.MEMBERSHIP on APPEAL.MEMBERSHIPPROGRAMID = MEMBERSHIP.MEMBERSHIPPROGRAMID
                inner join dbo.MEMBER M on M.MEMBERSHIPID = MEMBERSHIP.ID
                where (REVENUE_EXT.APPEALID is not null) and 
                        (r.TYPECODE in (1, 3) or --Pledge/MG Pledge

                        (r.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE in (0, 3, 5, 18, 19))) and --Payment (Gift,Recurring gift payment, Membership, Membership installment plan)

                    (cast(r.DATE as datetime) >= @STARTDATE or @STARTDATE is null) and
                    (cast(r.DATE as datetime) <= @ENDDATE or @ENDDATE is null) and
                    (r.CONSTITUENTID = m.CONSTITUENTID)
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                    and r.DELETEDON is null 
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                group by REVENUE_EXT.APPEALID;


                return;


            end