USP_MKTSEGMENTWHITEMAILREFRESH_GETCALCULATIONS

CREATE procedure dbo.[USP_MKTSEGMENTWHITEMAILREFRESH_GETCALCULATIONS]
(
  @SEGMENTID uniqueidentifier,
  @ASOFDATE datetime = null
)
as
  set nocount on;

  declare @RECORDSOURCEID uniqueidentifier;
  declare @GIFTIDSETREGISTERID uniqueidentifier;
  declare @RESPONDERS int;
  declare @RESPONSES int;
  declare @GIFTAMOUNT money;
  declare @ORGANIZATIONGIFTAMOUNT money;
  declare @SQL nvarchar(max);

  set @RESPONDERS = 0;
  set @RESPONSES = 0;
  set @GIFTAMOUNT = 0;
  set @ORGANIZATIONGIFTAMOUNT = 0;

  select
    @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
    @GIFTIDSETREGISTERID = [MKTSEGMENTWHITEMAILREFRESHPROCESS].[GIFTIDSETREGISTERID]
  from dbo.[MKTSEGMENTWHITEMAIL]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
  inner join dbo.[MKTSEGMENTWHITEMAILREFRESHPROCESS] on [MKTSEGMENTWHITEMAILREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENTWHITEMAIL].[ID]
  where [MKTSEGMENTWHITEMAIL].[ID] = @SEGMENTID;

  set @SQL = 'select' + char(13) +
             '  @RESPONDERS = count(distinct([GIFTIDSET].[DONORID])),' + char(13) + 
             '  @RESPONSES = count([GIFTIDSET].[DONORID]),' + char(13) + 
             '  @GIFTAMOUNT = isnull(sum([GIFTIDSET].[AMOUNT]), 0),' + char(13) +
             '  @ORGANIZATIONGIFTAMOUNT = isnull(sum([GIFTIDSET].[ORGANIZATIONAMOUNT]), 0)' + char(13) +
             'from dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@GIFTIDSETREGISTERID) + ' as [GIFTIDSET]';

  if @ASOFDATE is not null
    begin
      set @SQL = @SQL + char(13) + 'where [GIFTIDSET].[DATE] <= @ASOFDATE';
      exec sp_executesql @SQL, N'@ASOFDATE datetime, @RESPONDERS int output, @RESPONSES int output, @GIFTAMOUNT money output, @ORGANIZATIONGIFTAMOUNT money output', @ASOFDATE = @ASOFDATE, @RESPONDERS = @RESPONDERS output, @RESPONSES = @RESPONSES output, @GIFTAMOUNT = @GIFTAMOUNT output, @ORGANIZATIONGIFTAMOUNT = @ORGANIZATIONGIFTAMOUNT output;
    end
  else
    exec sp_executesql @SQL, N'@RESPONDERS int output, @RESPONSES int output, @GIFTAMOUNT money output, @ORGANIZATIONGIFTAMOUNT money output', @RESPONDERS = @RESPONDERS output, @RESPONSES = @RESPONSES output, @GIFTAMOUNT = @GIFTAMOUNT output, @ORGANIZATIONGIFTAMOUNT = @ORGANIZATIONGIFTAMOUNT output;

  select
    @RESPONDERS as [RESPONDERS],
    @RESPONSES as [RESPONSES],
    @GIFTAMOUNT as [TOTALGIFTAMOUNT],
    @ORGANIZATIONGIFTAMOUNT as [ORGANIZATIONTOTALGIFTAMOUNT],
    case when @RESPONSES > 0 then (@GIFTAMOUNT / @RESPONSES) else 0 end as [AVERAGEGIFTAMOUNT],
    case when @RESPONSES > 0 then (@ORGANIZATIONGIFTAMOUNT / @RESPONSES) else 0 end as [ORGANIZATIONAVERAGEGIFTAMOUNT];

  --declare @SPWRAP table ([RESPONDERS] integer, [RESPONSES] integer, [TOTALGIFTAMOUNT] money, [ORGANIZATIONTOTALGIFTAMOUNT] money, [AVERAGEGIFTAMOUNT] money, [ORGANIZATIONAVERAGEGIFTAMOUNT] money);

  --select * from @SPWRAP;


  return 0;