USP_REPORT_DESIGNATIONLEVELREVENUE_BYPURPOSE

            CREATE procedure dbo.USP_REPORT_DESIGNATIONLEVELREVENUE_BYPURPOSE
                (
                    @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);                        

                    select 
                        'http://www.blackbaud.com?DESIGNATIONLEVELID=' + CONVERT(nvarchar(36),DL.ID) as [DESIGNATIONLEVELID],
                        DL.NAME,
                        0 as SEQUENCE,
                        YEAR(@END) as PERIODYEAR,
                        coalesce(DLINFO.NUMGIFTS, 0) as TOTALNUMGIFTS,
                        coalesce(DLINFO.NUMDONORS, 0) TOTALDONORS,
                        0 as TOTALRAISED,
                        0 as PERIODNUMGIFTS,
                        coalesce(DLINFO.MAXGIFT, 0) MAXGIFT,
                        0 PERIODAVGGIFT,
                        coalesce(DLINFO.TOTALGIFT, 0) as OVERALLTOTALRAISED,
                        coalesce(DLRECEIVED.TOTALRECEIVED, 0) AS OVERALLTOTALRECEIVED,
                        coalesce(DLPLEDGE.TOTALPLEDGED - (DLPLEDGE.TOTALPAID + DLPLEDGE.TOTALWRITEOFFS), 0) as OVERALLTOTALEXPECTED,
                        case when DLINFO.NUMGIFTS > 0 then
                            DLINFO.TOTALGIFT / DLINFO.NUMGIFTS
                        else
                            0
                        end TOTALAVGGIFT,
                        0 as TOTALRECEIVED,
                        0 as TOTALEXPECTED,
                        0 as PERIODNUMDONORS,
                        NULL as APPEALNAME

                    from dbo.DESIGNATIONLEVEL DL 
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUECOUNTS(@START, @END, @CAMPAIGNHIERARCHYPATH) as DLINFO on DL.ID = DLINFO.DESIGNATIONLEVELID
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUERECEIVED(@START, @END, @CAMPAIGNHIERARCHYPATH) as DLRECEIVED on DL.ID = DLRECEIVED.DESIGNATIONLEVELID
                        left join dbo.UFN_DESIGNATIONLEVEL_REVENUEPLEDGED(@START, @END, @CAMPAIGNHIERARCHYPATH) as DLPLEDGE on DL.ID = DLPLEDGE.DESIGNATIONLEVELID
                    where
                        (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
                end