USP_STEWARDSHIPPACKAGEREPORT_GIVINGSUMMARY

            CREATE proc dbo.USP_STEWARDSHIPPACKAGEREPORT_GIVINGSUMMARY(@DESIGNATIONLEVELID uniqueidentifier, @CURRENTAPPUSERID uniqueidentifier = null, @REPORTUSERID nvarchar(128) = null)
                as

          if @CURRENTAPPUSERID is null
            set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETFROMLOGIN(@REPORTUSERID);

                    with DESIGNATIONS_CTE as (
                        select D.ID
                        from dbo.DESIGNATION D
                        where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
                              D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID or 
                              D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID or
                              D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID or
                              D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID
                    )
                    select
                        coalesce(sum(RS.AMOUNT - coalesce(WOS.AMOUNT, 0)), 0) TOTALREVENUE,
                        count(distinct R.CONSTITUENTID) TOTALDONORS,
                        count(distinct R.ID) TOTALDONATIONS,
                        case count(distinct R.ID) 
                            when 0 then 0 
                            else coalesce(sum(RS.AMOUNT - coalesce(WOS.AMOUNT, 0)), 0) / count(distinct R.ID) 
                        end as AVERAGEGIFT
                    from
                        dbo.REVENUESPLIT RS
                        inner join dbo.REVENUE R on R.ID = RS.REVENUEID                    
                        inner join DESIGNATIONS_CTE D on RS.DESIGNATIONID = D.ID
                        left join dbo.WRITEOFF WO on R.ID = WO.REVENUEID
                        left join dbo.WRITEOFFSPLIT WOS on WO.ID = WOS.WRITEOFFID and RS.DESIGNATIONID = WOS.DESIGNATIONID
                    where 
                        dbo.UFN_REVENUE_HASDESIGNATION(R.TRANSACTIONTYPECODE, RS.APPLICATIONCODE) = 1
            and (dbo.UFN_DESIGNATION_USERHASSITEACCESS(D.ID, @CURRENTAPPUSERID) = 1 or dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1)
                        and R.TRANSACTIONTYPECODE <> 3 --Ignore matching gift claims