USP_DATALIST_MKTSEGMENTPASSIVE_ACTIVATEDMAILINGS

CREATE procedure dbo.[USP_DATALIST_MKTSEGMENTPASSIVE_ACTIVATEDMAILINGS]
(
  @SEGMENTID uniqueidentifier
)
as
  set nocount on;

  select
    [MKTSEGMENTATIONSEGMENT].[ID],
    [MKTSEGMENTATION].[ID] as [MAILINGID],
    'CATALOG:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.envelope_16.png' as [IMAGEKEY],
    [MKTSEGMENTATION].[NAME] as [MAILINGNAME],
    [MKTSEGMENTATION].[ACTIVATEDATE] as [MAILINGACTIVATEDATE],
    [MKTPACKAGE].[NAME] as [PACKAGENAME],
    [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
    datediff(dd, [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE], [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE]) + 1 as  [EXPOSURE],
    [MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY] as [QUANTITY],
    [MKTSEGMENTATIONSEGMENTACTIVE].[RESPONDERS] as [RESPONDERS],
    [MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES] as [RESPONSES],
    [MKTSEGMENTATIONSEGMENTACTIVE].[VARIABLECOST] as [VARIABLECOST],
    [MKTSEGMENTATIONSEGMENTACTIVE].[FIXEDCOST] as [FIXEDCOST],
    [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST] as [TOTALCOST],
    cast((case when [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALGIFTAMOUNT] > 0 then [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST] / [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALGIFTAMOUNT] else 0 end) as money) as [COSTPERDOLLARRAISED],
    [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALGIFTAMOUNT] as [TOTALGIFTAMOUNT],
    cast((case when [MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES] > 0 then [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALGIFTAMOUNT] / [MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES] else 0 end) as money) as [AVERAGEGIFTAMOUNT],
    cast((case when [MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY] > 0 then cast([MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES] as decimal(19,4)) / cast([MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY] as decimal(19,4)) else 0 end) as decimal(19,4)) as [RESPONSERATE],
    cast((case when [MKTSEGMENTATIONACTIVE].[RESPONSERATE] > 0 then cast((case when [MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY] > 0 then (cast([MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES] as decimal(19,4)) / cast([MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY] as decimal(19,4))) * 100 else 0 end) as decimal(19,4)) / [MKTSEGMENTATIONACTIVE].[RESPONSERATE] else 0 end) as decimal(19,4)) as [LIFT],
    [MKTSEGMENTATIONSEGMENTACTIVE].[ROIAMOUNT] as [ROIAMOUNT],
    cast((case when [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST] > 0 then [MKTSEGMENTATIONSEGMENTACTIVE].[ROIAMOUNT] / [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST] else 0 end) as money) as [ROIPERCENT],
    cast((case when [MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDTOTALGIFTAMOUNT] > 0 then [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST] / [MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDTOTALGIFTAMOUNT] else 0 end) as money) as [EXPECTEDCOSTPERDOLLARRAISED],
    [MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDRESPONDERS] as [EXPECTEDRESPONDERS],
    [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT] as [EXPECTEDGIFTAMOUNT],
    [MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDTOTALGIFTAMOUNT] as [EXPECTEDTOTALGIFTAMOUNT],
    ([MKTSEGMENTATIONSEGMENT].[RESPONSERATE] / 100) as [EXPECTEDRESPONSERATE],
    [MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDROIAMOUNT] as [EXPECTEDROIAMOUNT],
    cast((case when [MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST] > 0 then cast([MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDROIAMOUNT] as decimal(19,4)) / cast([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST] as decimal(19,4)) else 0 end) as decimal(19,4)) as [EXPECTEDROIPERCENT],
    [MKTPACKAGE].[BASECURRENCYID]
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
  inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
  inner join dbo.[MKTSEGMENTATIONACTIVE] on [MKTSEGMENTATIONACTIVE].[ID] = [MKTSEGMENTATION].[ID]
  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
  where [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = @SEGMENTID
  and [MKTSEGMENTATION].[ACTIVE] = 1
  order by [MKTSEGMENTATION].[NAME];

  return 0;