USP_REPORT_DESIGNATIONLEVELREVENUE_BYAPPEAL

            -- WITH PLEDGES, MATCHING GIFTS, AND PLANNED GIFTS (AND THEIR PAYMENTS)

            -- IF BOTH THE PLEDGED REVENUE AND THE REVENUE PAYMENT DO NOT MATCH IN THE

            -- APPEAL FIELD, THESE NUMBERS WILL SEEM INCORRECT

            -- THIS MAY BE FIXED WHEN DESIGN DESIDES WHAT ACTION TO TAKE ON THIS

            CREATE procedure dbo.USP_REPORT_DESIGNATIONLEVELREVENUE_BYAPPEAL
                (
                    @START datetime = null,
                    @END datetime = null,
                    @CATEGORYCODEID uniqueidentifier = null,
                    @DESIGNATIONLEVELTYPEID uniqueidentifier = null,
                    @GROUPBY tinyint = 0,
                    @SELECTIONID uniqueidentifier = null,
                    @CAMPAIGNHIERARCHYPATH hierarchyid = null,
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
            as    
                begin
                    set @START = dbo.UFN_DATE_GETEARLIESTTIME(@START);
                    set @END = dbo.UFN_DATE_GETLATESTTIME(@END);

                    if @END < @START
                        raiserror('The start date must be on or before the end date.',13,1);                        

                    with 
                        APPEALRECEIVED_CTE as (
                            select AR.APPEALID,
                                AR.DESIGNATIONLEVELID,
                                SUM(AR.TOTALRECEIVED) as RECEIVED
                            from dbo.UFN_DESIGNATIONLEVEL_REVENUERECEIVED_BYAPPEAL(@START,@END, @GROUPBY,@CAMPAIGNHIERARCHYPATH) AR
                            group by AR.DESIGNATIONLEVELID, AR.APPEALID
                        ),
                        APPEALPLEDGE_CTE as (
                            select AP.APPEALID,
                                AP.DESIGNATIONLEVELID,
                                sum(AP.TOTALPLEDGED) - (sum(AP.TOTALPAID) + sum(AP.TOTALWRITEOFFS)) as EXPECTED
                            from dbo.UFN_DESIGNATIONLEVEL_REVENUEPLEDGED_BYAPPEAL(@START,@END, @GROUPBY,@CAMPAIGNHIERARCHYPATH) AP
                            group by AP.DESIGNATIONLEVELID, AP.APPEALID
                        ),
                        APPEALCOUNTS_CTE as (
                            select AC.APPEALID,
                                AC.DESIGNATIONLEVELID,
                                sum(AC.NUMDONORS) AS NUMDONORS,
                                SUM(AC.NUMGIFTS) AS NUMGIFTS,
                                MAX(AC.MAXGIFT) AS MAXGIFT,
                                SUM(AC.TOTALGIFT) AS TOTALGIFT
                            from dbo.UFN_DESIGNATIONLEVEL_REVENUECOUNTS_BYAPPEAL(@START,@END, @GROUPBY,@CAMPAIGNHIERARCHYPATH) AC
                            group by AC.DESIGNATIONLEVELID, AC.APPEALID
                        ),
                        APPEALS_CTE as (
                            select distinct 
                                case @GROUPBY 
                                    when 0 then APPEAL.ID
                                    when 1 then APPEALBUSINESSUNIT.BUSINESSUNITCODEID
                                    when 2 then APPEAL.APPEALREPORT1CODEID
                                    end as APPEALID,
                                case @GROUPBY when 0 then APPEAL.NAME
                                    when 1 then BUSINESSUNITCODE.DESCRIPTION
                                    when 2 then APPEALREPORTCODE.DESCRIPTION
                                    end as APPEALNAME
                            from dbo.APPEAL
                left join dbo.APPEALBUSINESSUNIT on APPEALBUSINESSUNIT.APPEALID = APPEAL.ID
                                left join dbo.BUSINESSUNITCODE on APPEALBUSINESSUNIT.BUSINESSUNITCODEID = BUSINESSUNITCODE.ID
                                left join dbo.APPEALREPORT1CODE APPEALREPORTCODE on APPEAL.APPEALREPORT1CODEID = APPEALREPORTCODE.ID
                        )
                    select distinct
                        'http://www.blackbaud.com?DESIGNATIONLEVELID=' + CONVERT(nvarchar(36),DL.ID) as [DESIGNATIONLEVELID],
                        DL.NAME,
                        1 as SEQUENCE,    
                        YEAR(@END) as PERIODYEAR, 
                        coalesce(DLOVERALLINFO.NUMGIFTS, 0) as TOTALNUMGIFTS,
                        coalesce(DLOVERALLINFO.NUMDONORS, 0) as TOTALDONORS,
                        (coalesce(AREC.RECEIVED, 0) + coalesce(AP.EXPECTED, 0)) as TOTALRAISED,
                        coalesce(AC.NUMGIFTS, 0) as PERIODNUMGIFTS,
                        coalesce(AC.MAXGIFT, 0) as MAXGIFT,
                        case 
                            when AC.NUMGIFTS > 0 then AC.TOTALGIFT / AC.NUMGIFTS
                            else 0
                            end PERIODAVGGIFT,
                        coalesce(DLOVERALLINFO.TOTALGIFT, 0) as OVERALLTOTALRAISED,
                        coalesce(OVERALLRECEIVED.TOTALRECEIVED, 0) AS OVERALLTOTALRECEIVED,
                        coalesce(OVERALLPLEDGE.TOTALPLEDGED - (OVERALLPLEDGE.TOTALPAID + OVERALLPLEDGE.TOTALWRITEOFFS), 0) as OVERALLTOTALEXPECTED,
                        case 
                            when DLOVERALLINFO.NUMGIFTS > 0 then DLOVERALLINFO.TOTALGIFT / DLOVERALLINFO.NUMGIFTS
                            else 0
                            end TOTALAVGGIFT,
                        coalesce(AREC.RECEIVED, 0) as TOTALRECEIVED,
                        coalesce(AP.EXPECTED, 0) as TOTALEXPECTED,
                        coalesce(AC.NUMDONORS, 0) as PERIODNUMDONORS,
                        A.APPEALNAME
                    from APPEALS_CTE as APPEALS
                        cross join dbo.DESIGNATIONLEVEL DL
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUECOUNTS(@START, @END,@CAMPAIGNHIERARCHYPATH) DLOVERALLINFO 
                            on DL.ID = DLOVERALLINFO.DESIGNATIONLEVELID
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUERECEIVED(@START, @END,@CAMPAIGNHIERARCHYPATH) OVERALLRECEIVED 
                            on DL.ID = OVERALLRECEIVED.DESIGNATIONLEVELID
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUEPLEDGED(@START, @END,@CAMPAIGNHIERARCHYPATH) OVERALLPLEDGE 
                            on DL.ID = OVERALLPLEDGE.DESIGNATIONLEVELID
                        left join APPEALRECEIVED_CTE AREC on DL.ID = AREC.DESIGNATIONLEVELID and APPEALS.APPEALID = AREC.APPEALID
                        left join APPEALPLEDGE_CTE AP on DL.ID = AP.DESIGNATIONLEVELID and APPEALS.APPEALID = AP.APPEALID
                        left join APPEALCOUNTS_CTE AC on DL.ID = AC.DESIGNATIONLEVELID and APPEALS.APPEALID = AC.APPEALID
                        left join APPEALS_CTE A on A.APPEALID = coalesce(AREC.APPEALID, AP.APPEALID)
                where
                    --(AP.EXPECTED > 0 OR AREC.RECEIVED > 0) and

                    (DL.DESIGNATIONLEVELCATEGORYCODEID = @CATEGORYCODEID or @CATEGORYCODEID is null) 
                    and (DL.DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID or @DESIGNATIONLEVELTYPEID IS NULL) 
                    and (@SELECTIONID is null or DL.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID)))
                    and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,DL.SITEID) = 1
                order by DL.NAME, A.APPEALNAME
            end