USP_REPORT_DESIGNATIONLEVELREVENUE_BYDATE

            CREATE procedure dbo.USP_REPORT_DESIGNATIONLEVELREVENUE_BYDATE
                (
                    @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,
                        PERIODS.SEQUENCE,
                        PERIODS.PERIODYEAR,
                        coalesce(DLOVERALLINFO.NUMGIFTS, 0) as TOTALNUMGIFTS,
                        coalesce(DLOVERALLINFO.NUMDONORS, 0) as TOTALDONORS,
                        (coalesce(RECEIVED.TOTALRECEIVED, 0) + coalesce(PLEDGE.TOTALPLEDGED - (PLEDGE.TOTALPAID + PLEDGE.TOTALWRITEOFFS), 0)) as TOTALRAISED,
                        coalesce(DLINFO.NUMGIFTS, 0) as PERIODNUMGIFTS,
                        coalesce(DLINFO.MAXGIFT, 0) as MAXGIFT,
                        case when DLINFO.NUMGIFTS > 0 then
                            DLINFO.TOTALGIFT / DLINFO.NUMGIFTS
                        else
                            0
                        end PERIODAVGGIFT,
                        DLOVERALLINFO.TOTALGIFT 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(RECEIVED.TOTALRECEIVED, 0) as TOTALRECEIVED,
                        coalesce(PLEDGE.TOTALPLEDGED - (PLEDGE.TOTALPAID + PLEDGE.TOTALWRITEOFFS), 0) as TOTALEXPECTED,
                        coalesce(DLINFO.NUMDONORS, 0) as PERIODNUMDONORS,
                        NULL as APPEALNAME
                    from dbo.UFN_DESIGNATIONREPORT_GETPERIODS(@START, @END, @GROUPBY) PERIODS
                            cross join dbo.DESIGNATIONLEVEL DL
                            left join dbo.UFN_DESIGNATIONLEVEL_REVENUECOUNTS_BYPERIOD(@START, @END, @GROUPBY,@CAMPAIGNHIERARCHYPATH) DLINFO 
                                    on DL.ID = DLINFO.DESIGNATIONLEVELID and DLINFO.STARTDATE = PERIODS.STARTDATE
                            left join dbo.UFN_DESIGNATIONLEVEL_REVENUERECEIVED_BYPERIOD(@START, @END, @GROUPBY,@CAMPAIGNHIERARCHYPATH) RECEIVED 
                                    on DL.ID = RECEIVED.DESIGNATIONLEVELID and PERIODS.STARTDATE = RECEIVED.STARTDATE 
                            left join dbo.UFN_DESIGNATIONLEVEL_REVENUEPLEDGED_BYPERIOD(@START, @END, @GROUPBY,@CAMPAIGNHIERARCHYPATH) PLEDGE 
                                    on DL.ID = PLEDGE.DESIGNATIONLEVELID and PERIODS.STARTDATE = PLEDGE.STARTDATE 
                            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
                    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, PERIODS.PERIODYEAR, PERIODS.SEQUENCE
                end