UFN_DESIGNATION_RAISEDBYAPPEAL

        CREATE function dbo.UFN_DESIGNATION_RAISEDBYAPPEAL
            (@STARTDATE datetime, @ENDDATE datetime)
            returns @REVENUEINFO table
            (
                APPEALID uniqueidentifier NOT NULL,
                DESID uniqueidentifier NOT NULL,
                DESNAME nvarchar(500) NOT NULL,
                DESTOTALRECEIVED money NOT NULL,
                DESTOTALRECEIVED_REGULAR money NOT NULL,
                DESGROSSAMOUNT 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, 
                        d.ID DESID, 
                        d.NAME DESNAME,
                        coalesce(sum(rs.AMOUNT),0) DESTOTALRECEIVED,
                        0 DESTOTALRECEIVED_REGULAR,
                        coalesce(sum(rs.AMOUNT),0) + coalesce(sum(rsga.TAXCLAIMAMOUNT),0) DESGROSSAMOUNT
                    from dbo.REVENUE r

                    inner join dbo.REVENUESPLIT rs 
                        on r.ID=rs.REVENUEID

                    left outer join dbo.REVENUESPLITGIFTAID rsga
                        on rs.ID = rsga.ID

                    inner join dbo.DESIGNATION d 
                        on rs.DESIGNATIONID=d.ID

                    where (r.APPEALID is not null) and 
                        (r.DATE >= @STARTDATE or @STARTDATE is null) and
                        (r.DATE <= @ENDDATE or @ENDDATE is null) and                          
                         r.TRANSACTIONTYPECODE = 0         
                    group by r.APPEALID, d.ID, d.NAME;

                -- update table to include total received from regular gifts (recurring gift payments)

                update @REVENUEINFO set
                    DESTOTALRECEIVED_REGULAR = 
                            (
                                select
                                    coalesce(sum(rs.AMOUNT),0)
                                from dbo.REVENUE r

                                inner join dbo.REVENUESPLIT rs 
                                        on r.ID=rs.REVENUEID

                                where (r.APPEALID = info.APPEALID) and
                                    (rs.DESIGNATIONID = info.DESID) and
                                    (r.DATE >= @STARTDATE or @STARTDATE is null) and
                                    (r.DATE <= @ENDDATE or @ENDDATE is null) and 
                                     r.TRANSACTIONTYPECODE = 0 and
                                     rs.APPLICATIONCODE = 3  
                            )
                from @REVENUEINFO info;                

                return;


            end