USP_KPI_MAJORGIVING_AVGDAYSTOCLOSEGIFTFUNDRAISER

        CREATE procedure dbo.USP_KPI_MAJORGIVING_AVGDAYSTOCLOSEGIFTFUNDRAISER
          @VALUE int output,
          @ASOFDATE datetime, 
          @DATEFILTER tinyint = null,
          @PROSPECTPLANTYPECODEID uniqueidentifier = null,
          @OPPORTUNITYAMOUNTBRACKETID uniqueidentifier = null,
          @DESIGNATIONID uniqueidentifier = null,
          @FUNDRAISERID uniqueidentifier = null,
          @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
          @CURRENTAPPUSERID uniqueidentifier = null
        as

          set nocount on;

          if @DATEFILTER is null begin
            set @DATEFILTER = 10;  -- all dates;

          end

          declare @STARTDATE datetime;
          declare @ENDDATE datetime;

          exec dbo.USP_RESOLVEDATEFILTER @DATEFILTER=@DATEFILTER, @STARTDATE=@STARTDATE output, @ENDDATE=@ENDDATE output, @ASOFDATE=@ASOFDATE;

          declare @DAYS int;
          declare @ASKS int;

          if @ORGPOSITIONSSELECTIONID is null
            select 
              @ASKS = count(*),
              @DAYS = sum(case when I.FIRSTDATE>O.RESPONSEDATE then 0 else datediff(day, I.FIRSTDATE, O.RESPONSEDATE) end)
            from 
              dbo.OPPORTUNITY O
              inner join dbo.PROSPECTPLAN PP on PP.ID=O.PROSPECTPLANID
              inner join (
                select min(DATE) FIRSTDATE, PROSPECTPLANID 
                from dbo.INTERACTION 
                group by PROSPECTPLANID
              ) I on I.PROSPECTPLANID=PP.ID
              left outer join dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) R on dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID,R.BASECURRENCYID) between R.LOWERLIMIT and R.UPPERLIMIT
            where
              O.STATUSCODE=3
              and O.RESPONSEDATE between @STARTDATE and @ENDDATE
              and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
              and (@FUNDRAISERID is null or exists (select 1 from dbo.UFN_FUNDRAISER_PROSPECTPLANS(@FUNDRAISERID) FPP where PP.ID = FPP.ID))
              and (@DESIGNATIONID is null or exists(select 1 from dbo.OPPORTUNITYDESIGNATION OD where OD.OPPORTUNITYID=O.ID and OD.DESIGNATIONID=@DESIGNATIONID))
              and (@OPPORTUNITYAMOUNTBRACKETID is null or R.ID = @OPPORTUNITYAMOUNTBRACKETID)
          else
          begin
            declare @IDS as table(ID uniqueidentifier);
            insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, null;

            select 
              @ASKS = count(*),
              @DAYS = sum(case when I.FIRSTDATE>O.RESPONSEDATE then 0 else datediff(day, I.FIRSTDATE, O.RESPONSEDATE) end)
            from 
              dbo.OPPORTUNITY O
              inner join dbo.PROSPECTPLAN PP on PP.ID=O.PROSPECTPLANID
              inner join (
                select min(DATE) FIRSTDATE, PROSPECTPLANID 
                from dbo.INTERACTION 
                group by PROSPECTPLANID
              ) I on I.PROSPECTPLANID=PP.ID
              inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
                  on (OPH.CONSTITUENTID = PP.PRIMARYMANAGERFUNDRAISERID) or (OPH.CONSTITUENTID = PP.SECONDARYMANAGERFUNDRAISERID) or ((select count(ID) from dbo.SECONDARYFUNDRAISER SF where SF.PROSPECTPLANID = PP.ID and SF.FUNDRAISERID = OPH.CONSTITUENTID) > 0) 
                  and O.RESPONSEDATE between OPH.DATEFROM and coalesce(OPH.DATETO, O.RESPONSEDATE)
              inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
              left outer join dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) R on dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID,R.BASECURRENCYID) between R.LOWERLIMIT and R.UPPERLIMIT
            where
              O.STATUSCODE=3
              and O.RESPONSEDATE between @STARTDATE and @ENDDATE
              and (@PROSPECTPLANTYPECODEID is null or PP.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
              and (@FUNDRAISERID is null or exists (select 1 from dbo.UFN_FUNDRAISER_PROSPECTPLANS(@FUNDRAISERID) FPP where PP.ID = FPP.ID))
              and (@DESIGNATIONID is null or exists(select 1 from dbo.OPPORTUNITYDESIGNATION OD where OD.OPPORTUNITYID=O.ID and OD.DESIGNATIONID=@DESIGNATIONID))
              and (@OPPORTUNITYAMOUNTBRACKETID is null or  R.ID = @OPPORTUNITYAMOUNTBRACKETID)
          end

          if @ASKS>0 begin
            set @VALUE = convert(int,convert(decimal,@DAYS) / @ASKS + 0.5);
          end else begin
            set @VALUE = null;
          end