USP_DATALIST_APPEALMAILINGRESPONSEINFO

CREATE procedure dbo.USP_DATALIST_APPEALMAILINGRESPONSEINFO
(
  @MAILINGID uniqueidentifier
)
as
  set nocount on;

  declare @UNRESOLVEDRESPONSECOUNTS table([RESPONDERS] int, [RESPONSES] int, [TOTALGIFTAMOUNT] money, [AVERAGEGIFTAMOUNT] money, [ORGANIZATIONTOTALGIFTAMOUNT] money, [ORGANIZATIONAVERAGEGIFTAMOUNT] money);
  declare @BASECURRENCYID uniqueidentifier;
  select @BASECURRENCYID = [BASECURRENCYID] from dbo.[MKTSEGMENTATION] where [ID] = @MAILINGID;

  insert into @UNRESOLVEDRESPONSECOUNTS
  exec dbo.[USP_MKTSEGMENTATION_GETUNRESOLVEDRESPONSECOUNTS] @MAILINGID;

  select
    'Total (Direct + Indirect)' as [RESPONSETYPE],
    [RESPONDERS],
    [RESPONSES],
    [TOTALGIFTAMOUNT],
    [AVERAGEGIFTAMOUNT],
    [COSTPERDOLLARRAISED],
    convert(decimal(20,2), [RESPONSERATE]),
    [ROIAMOUNT],
    convert(decimal(20,2), [ROIPERCENT]),
    @BASECURRENCYID
  from dbo.[MKTSEGMENTATIONACTIVE]
  where [ID] = @MAILINGID

  union all

  select
    'Direct' as [RESPONSETYPE],
    [RESPONDERS] - [INDIRECTRESPONDERS] as [RESPONDERS],
    [RESPONSES] - [INDIRECTRESPONSES] as [RESPONSES],
    [TOTALGIFTAMOUNT] - [INDIRECTTOTALGIFTAMOUNT] as [TOTALGIFTAMOUNT],
    (case when ([RESPONSES] - [INDIRECTRESPONSES]) > 0 then ([TOTALGIFTAMOUNT] - [INDIRECTTOTALGIFTAMOUNT]) / cast(([RESPONSES] - [INDIRECTRESPONSES]) as money) else 0 end) as [AVERAGEGIFTAMOUNT],
    (case when ([TOTALGIFTAMOUNT] - [INDIRECTTOTALGIFTAMOUNT]) > 0 then [TOTALCOST] / ([TOTALGIFTAMOUNT] - [INDIRECTTOTALGIFTAMOUNT]) else 0 end) as [COSTPERDOLLARRAISED],
    convert(decimal(20,2), (case when [QUANTITY] > 0 then ((cast([RESPONSES] as float) - [INDIRECTRESPONSES]) / [QUANTITY]) * 100 else 0 end)) as [RESPONSERATE],
    [TOTALGIFTAMOUNT] - [INDIRECTTOTALGIFTAMOUNT] - [TOTALCOST] as [ROIAMOUNT],
    convert(decimal(20,2), (case when [TOTALCOST] > 0 then (([TOTALGIFTAMOUNT] - [INDIRECTTOTALGIFTAMOUNT] - [TOTALCOST]) / [TOTALCOST]) * 100 else 0 end)) as [ROIPERCENT],
    @BASECURRENCYID
  from dbo.[MKTSEGMENTATIONACTIVE]
  where [ID] = @MAILINGID

  union all

  select
    'Indirect' as [RESPONSETYPE],
    [INDIRECTRESPONDERS],
    [INDIRECTRESPONSES],
    [INDIRECTTOTALGIFTAMOUNT],
    [INDIRECTAVERAGEGIFTAMOUNT],
    0 as [COSTPERDOLLARRAISED],
    0.00 as [RESPONSERATE],
    0 as [ROIAMOUNT],
    0.00 as [ROIPERCENT],
    @BASECURRENCYID
  from dbo.[MKTSEGMENTATIONACTIVE]
  where [ID] = @MAILINGID

  union all

  select
    'Unresolved' as [RESPONSETYPE],
    [RESPONDERS],
    [RESPONSES],
    [TOTALGIFTAMOUNT],
    [AVERAGEGIFTAMOUNT],
    0 as [COSTPERDOLLARRAISED],
    0.00 as [RESPONSERATE],
    0 as [ROIAMOUNT],
    0.00 as [ROIPERCENT],
    @BASECURRENCYID
  from @UNRESOLVEDRESPONSECOUNTS;