USP_MKTSEGMENTATION_GETUNRESOLVEDRESPONSECOUNTS

CREATE procedure dbo.USP_MKTSEGMENTATION_GETUNRESOLVEDRESPONSECOUNTS
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  declare @RECORDSOURCEID uniqueidentifier;
  declare @TOTALRESPONDERS int;
  declare @TOTALRESPONSES int;
  declare @TOTALGIFTAMOUNT money;
  declare @RESPONDERS int;
  declare @RESPONSES int;
  declare @GIFTAMOUNT money;
  declare @SQL nvarchar(max);
  declare @PARAMDEF nvarchar(255);
  declare @GIFTIDSETID uniqueidentifier;
  declare @ORGANIZATIONGIFTAMOUNT money;
  declare @ORGANIZATIONTOTALGIFTAMOUNT money;

  set @TOTALRESPONDERS = 0;
  set @TOTALRESPONSES = 0;
  set @TOTALGIFTAMOUNT = 0;
  set @ORGANIZATIONTOTALGIFTAMOUNT = 0;

  set @PARAMDEF = '@RESPONDERS int output, ' +
                  '@RESPONSES int output, ' +
                  '@GIFTAMOUNT money output, ' +
                  '@ORGANIZATIONGIFTAMOUNT money output';


  declare RECORDSOURCECURSOR cursor local fast_forward for
    select [QUERYVIEWCATALOGID]
    from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID);

  open RECORDSOURCECURSOR;
  fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;

  while (@@FETCH_STATUS = 0)
  begin
    /* Gather some info so we can build the SQL for each record source */
    select
      @GIFTIDSETID = [UNRESOLVEDGIFTIDSETREGISTERID]
    from dbo.[MKTSEGMENTATIONACTIVATE]
    where [SEGMENTATIONID] = @SEGMENTATIONID
    and [RECORDSOURCEID] = @RECORDSOURCEID;

    /* Build the SQL statement for this record source */
    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](@GIFTIDSETID) + ' as [GIFTIDSET]';

    /* Execute the SQL to get the counts/amounts for this record source */
    exec sp_executesql @SQL, @PARAMDEF, 
      @RESPONDERS = @RESPONDERS output, 
      @RESPONSES = @RESPONSES output, 
      @GIFTAMOUNT = @GIFTAMOUNT output,
      @ORGANIZATIONGIFTAMOUNT = @ORGANIZATIONGIFTAMOUNT output;

    /* Keep the running totals for all record sources */
    set @TOTALRESPONDERS = @TOTALRESPONDERS + @RESPONDERS;
    set @TOTALRESPONSES = @TOTALRESPONSES + @RESPONSES;
    set @TOTALGIFTAMOUNT = @TOTALGIFTAMOUNT + @GIFTAMOUNT;
    set @ORGANIZATIONTOTALGIFTAMOUNT = @ORGANIZATIONTOTALGIFTAMOUNT + @ORGANIZATIONGIFTAMOUNT;

    fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
  end;

  close RECORDSOURCECURSOR;
  deallocate RECORDSOURCECURSOR;

  /* Return the values */
  select
    @TOTALRESPONDERS as [RESPONDERS],
    @TOTALRESPONSES as [RESPONSES],
    @TOTALGIFTAMOUNT as [TOTALGIFTAMOUNT],
    (case when @TOTALRESPONSES = 0 then 0 else (@TOTALGIFTAMOUNT / cast(@TOTALRESPONSES as money)) end) as [AVERAGEGIFTAMOUNT],
    @ORGANIZATIONTOTALGIFTAMOUNT as [ORGANIZATIONTOTALGIFTAMOUNT],
    (case when @TOTALRESPONSES = 0 then 0 else (@ORGANIZATIONTOTALGIFTAMOUNT / cast(@TOTALRESPONSES as money)) end) as [ORGANIZATIONAVERAGEGIFTAMOUNT];

  return 0;