USP_MKTSEGMENT_GETSEGMENTATIONACTIVEDATALIST

create procedure dbo.[USP_MKTSEGMENT_GETSEGMENTATIONACTIVEDATALIST]
(
  @SEGMENTID uniqueidentifier
)
as
  set nocount on;

  /* If a segment in a mailing has test segments, then rollup/average/etc the test segment information */
  /* into the segment's, because we only want to display one row for each mailing. */
  select
    [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],
    (case when exists(select 1 from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]) then 1 else 0 end) as [HASTESTSEGMENTS],
    (case (select count(distinct([PACKAGEID])) from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]) when 0 then [MKTPACKAGE].[NAME] when 1 then (case when [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = (select distinct [PACKAGEID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]) then [MKTPACKAGE].[NAME] else '<Multiple>' end) else '<Multiple>' end) as [PACKAGENAME],
    sum([MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY]) as [QUANTITY],
    sum([MKTSEGMENTATIONSEGMENTACTIVE].[RESPONDERS]) as [RESPONDERS],
    sum([MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES]) as [RESPONSES],
    sum([MKTSEGMENTATIONSEGMENTACTIVE].[VARIABLECOST]) as [VARIABLECOST],
    sum([MKTSEGMENTATIONSEGMENTACTIVE].[FIXEDCOST]) as [FIXEDCOST],
    sum([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST]) as [TOTALCOST],
    cast((case when sum([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALGIFTAMOUNT]) > 0 then sum([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST]) / sum([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALGIFTAMOUNT]) else 0 end) as money) as [COSTPERDOLLARRAISED],
    sum([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALGIFTAMOUNT]) as [TOTALGIFTAMOUNT],
    cast((case when sum([MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES]) > 0 then sum([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALGIFTAMOUNT]) / sum([MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES]) else 0 end) as money) as [AVERAGEGIFTAMOUNT],
    cast((case when sum([MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY]) > 0 then sum(cast([MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES] as decimal(19,4))) / sum(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 sum([MKTSEGMENTATIONSEGMENTACTIVE].[QUANTITY]) > 0 then (sum(cast([MKTSEGMENTATIONSEGMENTACTIVE].[RESPONSES] as decimal(19,4))) / sum(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],
    sum([MKTSEGMENTATIONSEGMENTACTIVE].[ROIAMOUNT]) as [ROIAMOUNT],
    cast((case when sum([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST]) > 0 then sum([MKTSEGMENTATIONSEGMENTACTIVE].[ROIAMOUNT]) / sum([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST]) else 0 end) as money) as [ROIPERCENT],
    cast((case when sum([MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDTOTALGIFTAMOUNT]) > 0 then sum([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST]) / sum([MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDTOTALGIFTAMOUNT]) else 0 end) as money) as [EXPECTEDCOSTPERDOLLARRAISED],
    sum([MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDRESPONDERS]) as [EXPECTEDRESPONDERS],
    ([MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT] + isnull((select sum([GIFTAMOUNT]) from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]),0)) / (1 + (select count(1) from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID])) as [EXPECTEDGIFTAMOUNT],
    sum([MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDTOTALGIFTAMOUNT]) as [EXPECTEDTOTALGIFTAMOUNT],
    (([MKTSEGMENTATIONSEGMENT].[RESPONSERATE] + isnull((select sum([RESPONSERATE]) from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]),0)) / (1 + (select count(1) from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]))) / 100 as [EXPECTEDRESPONSERATE],
    sum([MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDROIAMOUNT]) as [EXPECTEDROIAMOUNT],
    cast((case when sum([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST]) > 0 then sum(cast([MKTSEGMENTATIONSEGMENTACTIVE].[EXPECTEDROIAMOUNT] as decimal(19,4))) / sum(cast([MKTSEGMENTATIONSEGMENTACTIVE].[TOTALCOST] as decimal(19,4))) else 0 end) as decimal(19,4)) as [EXPECTEDROIPERCENT],
    [MKTSEGMENTATIONSEGMENT].[BASECURRENCYID],
    [MKTSEGMENTATIONSEGMENT].[EXCLUDE]
  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]
  left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
  where [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = @SEGMENTID
  and [MKTSEGMENTATION].[ACTIVE] = 1
  group by [MKTSEGMENTATION].[ID], [MKTSEGMENTATION].[NAME], [MKTSEGMENTATION].[ACTIVATEDATE], [MKTSEGMENTATIONSEGMENT].[ID], [MKTSEGMENTATIONSEGMENT].[PACKAGEID], [MKTPACKAGE].[NAME], [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT], [MKTSEGMENTATIONSEGMENT].[RESPONSERATE], [MKTSEGMENTATIONACTIVE].[RESPONSERATE], [MKTSEGMENTATIONSEGMENT].[BASECURRENCYID], [MKTSEGMENTATIONSEGMENT].[EXCLUDE]
  order by [MKTSEGMENTATION].[NAME];

  return 0;