UFN_MEMBER_PLEDGEDBYAPPEAL

            create function dbo.UFN_MEMBER_PLEDGEDBYAPPEAL
            (@STARTDATE datetime, @ENDDATE datetime)
            returns @REVENUEINFO table
            (
                APPEALID uniqueidentifier NOT NULL,
                TOTALPLEDGED money NOT NULL,
                PLEDGESPAID money NOT NULL,
                PLEDGESWRITTENOFF money NOT NULL
            )
            as
            begin

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

                insert into @REVENUEINFO

                    select r.APPEALID APPEALID, 
                        coalesce(sum(r.AMOUNT),0) TOTALPLEDGED, 
                        (select coalesce(sum(payrevspl.AMOUNT), 0) 
                             from dbo.REVENUESPLIT payrevspl 
                                inner join dbo.REVENUE payrev on payrevspl.REVENUEID = payrev.ID
                             where 
                                payrevspl.ID in (
                                    select ip.PAYMENTID
                                        from dbo.INSTALLMENTPAYMENT ip
                                            inner join dbo.REVENUE pledgerev on ip.PLEDGEID = pledgerev.ID
                                            inner join dbo.REVENUESPLIT pledgerevspl on pledgerev.ID = pledgerevspl.REVENUEID
                                            inner join dbo.APPEAL a on a.ID = r.APPEALID
                                            inner join dbo.MEMBER on PLEDGErev.CONSTITUENTID = MEMBER.CONSTITUENTID
                                            inner join dbo.MEMBERSHIP on MEMBERSHIP.MEMBERSHIPPROGRAMID = A.MEMBERSHIPPROGRAMID
                                        where (pledgerev.DATE >= @STARTDATE or @STARTDATE is null) and
                                            (MEMBER.MEMBERSHIPID = MEMBERSHIP.ID) and 
                                            (pledgerev.DATE <= @ENDDATE or @ENDDATE is null) and 
                                            pledgerev.APPEALID=r.APPEALID) and
                                (payrev.APPEALID=r.APPEALID) and 
                                (payrev.DATE >= @STARTDATE or @STARTDATE is null) and
                                (payrev.DATE <= @ENDDATE or @ENDDATE is null)) PLEDGESPAID,
                        (select coalesce(sum(wos.AMOUNT),0) 
                            from dbo.WRITEOFFSPLIT wos
                                inner join dbo.WRITEOFF wo on wos.WRITEOFFID = wo.ID
                                inner join dbo.REVENUE worev on wo.REVENUEID = worev.ID
                                inner join dbo.APPEAL a on a.ID = r.APPEALID
                                inner join dbo.MEMBER on worev.CONSTITUENTID = MEMBER.CONSTITUENTID
                            inner join dbo.MEMBERSHIP on MEMBERSHIP.MEMBERSHIPPROGRAMID = A.MEMBERSHIPPROGRAMID
                            where
                                (worev.DATE >= @STARTDATE or @STARTDATE is null) and
                                (worev.DATE <= @ENDDATE or @ENDDATE is null) and
                                (worev.APPEALID=r.APPEALID)and
                                MEMBER.MEMBERSHIPID = MEMBERSHIP.id)PLEDGESWRITTENOFF
                        from dbo.REVENUE r
                            inner join dbo.REVENUESPLIT rs on r.ID=rs.REVENUEID
                            inner join dbo.APPEAL a on a.ID = r.APPEALID
                            inner join dbo.MEMBER on r.CONSTITUENTID = MEMBER.CONSTITUENTID
                            inner join dbo.MEMBERSHIP on MEMBERSHIP.MEMBERSHIPPROGRAMID = A.MEMBERSHIPPROGRAMID
                        where (r.APPEALID is not null) and r.TRANSACTIONTYPECODE=1 and 
                            ((r.DATE >= @STARTDATE) or @STARTDATE is null) and
                            ((r.DATE <= @ENDDATE)or @ENDDATE is null) and
                            (MEMBER.MEMBERSHIPID = MEMBERSHIP.ID) and
                            (A.MEMBERSHIPPROGRAMID is not null)
                        group by r.APPEALID


                return;


            end