USP_KPI_MARKETING_MAILING_AVERAGEGIFTAMOUNT_INTERNAL

CREATE procedure dbo.[USP_KPI_MARKETING_MAILING_AVERAGEGIFTAMOUNT_INTERNAL]
(
  @ASOFDATE datetime,
  @SEGMENTATIONID uniqueidentifier = null,
  @VALUE money = 0 output,
  @CURRENCYID uniqueidentifier = null
)
as
  set nocount on;

  declare @MAILINGID uniqueidentifier;
  declare @DATEREFRESHED date;
  declare @TOTALGIFTAMOUNT money;
  declare @RESPONSES int;

  declare @RESPONSECOUNTS table
  (
    [OFFERS] int, 
    [RESPONDERS] int, 
    [RESPONSES] int, 
    [TOTALGIFTAMOUNT] money, 
    [RESPONSERATE] decimal(19,4), 
    [ORGANIZATIONTOTALGIFTAMOUNT] money,
    [FIRSTRESPONSEDATE] datetime
  );

  declare @ORGANIZATIONCURRENCYID uniqueidentifier;

  set @TOTALGIFTAMOUNT = 0;
  set @RESPONSES = 0;
  set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  declare MAILINGSCURSOR cursor local fast_forward for
    select
      [MKTSEGMENTATION].[ID],
      cast(isnull([MKTSEGMENTATIONREFRESHPROCESS].[DATEREFRESHED], [MKTSEGMENTATION].[ACTIVATEDATE]) as date)
    from dbo.[MKTSEGMENTATION]
    /*#IDSETEXTENSION*/
    inner join dbo.[MKTSEGMENTATIONREFRESHPROCESS] on [MKTSEGMENTATIONREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    where (@SEGMENTATIONID is null or @SEGMENTATIONID = [MKTSEGMENTATION].[ID]);

  open MAILINGSCURSOR;
  fetch next from MAILINGSCURSOR into @MAILINGID, @DATEREFRESHED;

  /*
  for KPIs based on a single marketing effort, CURRENCYID will be either the mailing's base currency or the organization currency
  for KPIs based on a selection of marketing efforts, CURRENCYID will be the organization currency
  */

  while (@@FETCH_STATUS = 0)
    begin
      /* If the "As of date" is the same day or after the date the mailing was last refreshed, then grab */
      /* the value from the mailing cache, otherwise perform the calculation using the "As of date". */
      if @ASOFDATE >= @DATEREFRESHED
        select
          @TOTALGIFTAMOUNT = @TOTALGIFTAMOUNT +
            case when @CURRENCYID = @ORGANIZATIONCURRENCYID
                 then [ORGANIZATIONTOTALGIFTAMOUNT]
                 else [TOTALGIFTAMOUNT]
            end,
          @RESPONSES = @RESPONSES + [RESPONSES]
        from dbo.[MKTSEGMENTATIONACTIVE]
        where [ID] = @MAILINGID;
      else
        begin
          delete from @RESPONSECOUNTS;

          insert into @RESPONSECOUNTS
            exec dbo.[USP_MKTSEGMENTATION_GETRESPONSECOUNTS] @MAILINGID, @ASOFDATE;

          select
            @TOTALGIFTAMOUNT = @TOTALGIFTAMOUNT +
            case when @CURRENCYID = @ORGANIZATIONCURRENCYID
                 then [ORGANIZATIONTOTALGIFTAMOUNT]
                 else [TOTALGIFTAMOUNT]
            end,
            @RESPONSES = @RESPONSES + [RESPONSES]
          from @RESPONSECOUNTS;
        end

      fetch next from MAILINGSCURSOR into @MAILINGID, @DATEREFRESHED;
    end

  close MAILINGSCURSOR;
  deallocate MAILINGSCURSOR;

  set @VALUE = cast((case when @RESPONSES > 0 then @TOTALGIFTAMOUNT / @RESPONSES else 0 end) as money);