USP_REPORT_APPEALPROGRESSSUMMARY_MAIN

            create procedure dbo.USP_REPORT_APPEALPROGRESSSUMMARY_MAIN
            as 
            select 
                APPEAL.NAME,
                APPEAL.GOAL,
                (
                    select count(distinct CONSTITUENTAPPEAL.CONSTITUENTID) 
                    from dbo.CONSTITUENTAPPEAL
                    where CONSTITUENTAPPEAL.APPEALID = APPEAL.ID
                ) as [NUMBERSOLICITED],
                coalesce(( --Get gifts, pledges, and recurring gift payments

                    select coalesce(sum(AMOUNT), 0) 
                    from dbo.REVENUE 
                    /*#IDSETEXTENSION*/
                    where REVENUE.APPEALID = APPEAL.ID
                          and REVENUE.TYPECODE in (0, 1, 3, 5)
                ), 0)
                - 
                coalesce(( --Subtract write-offs of the above pledges

                    select (select sum(AMOUNT) from dbo.INSTALLMENTWRITEOFF where WRITEOFFID = WRITEOFF.ID)
                    from dbo.WRITEOFF
                    left join dbo.REVENUE on REVENUE.ID = WRITEOFF.REVENUEID
                    where REVENUE.APPEALID = APPEAL.ID
                ), 0) as [TOTALREVENUE],
                coalesce(( --Get gifts, pledge payments, and recurring gift payments

                    select coalesce(sum(AMOUNT), 0)
                    from dbo.REVENUE
                    where REVENUE.APPEALID = APPEAL.ID
                          and REVENUE.TYPECODE in (0, 4, 5)
                ), 0) as [ACTUALREVENUE],
                (
                    select count(REVENUE.ID)
                    from dbo.REVENUE 
                    where REVENUE.APPEALID = APPEAL.ID
                          and REVENUE.TYPECODE in (0, 1, 3, 5)
                ) as [NUMBEROFGIFTS],
                (
                    select count(distinct CONSTITUENTID)
                    from dbo.REVENUE
                    where REVENUE.APPEALID = APPEAL.ID
                          and REVENUE.TYPECODE in (0, 1, 3, 5)
                ) as [NUMBEROFDONORS],
                (
                    select coalesce(avg(AMOUNT), 0)
                    from dbo.REVENUE 
                    where REVENUE.APPEALID = APPEAL.ID
                          and REVENUE.TYPECODE in (0, 1, 3, 5)
                ) as [AVGGIFTAMOUNT]    
            from dbo.APPEAL 
            /*#IDSETEXTENSION*/
            where APPEAL.ISACTIVE = 1