USP_DATALIST_MKTSEGMENT_NONACTIVATEDMAILINGS

CREATE procedure dbo.[USP_DATALIST_MKTSEGMENT_NONACTIVATEDMAILINGS]
(
  @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, 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],
    (case when count([MKTSEGMENTATIONTESTSEGMENT].[ID]) > 0 then 1 else 0 end) as [HASTESTSEGMENTS],
    (case when count(distinct([MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID])) > 0 and [MKTSEGMENTATIONSEGMENT].[PACKAGEID] not in (select [PACKAGEID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]) then '<Multiple>' else [MKTPACKAGE].[NAME] end) as [PACKAGENAME],
    cast([MKTSEGMENTATIONSEGMENT].[SAMPLESIZE] as nvarchar(10)) + (case when [MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPECODE] = 0 then '%' else '' end) as [SAMPLESIZE],
    (([MKTPACKAGE].[UNITCOST] + isnull(sum([TESTPACKAGE].[UNITCOST]),0)) / (1 + count([MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID])) + dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD2]([MKTSEGMENTATIONSEGMENT].[ID], 0)) as [COSTPERPIECE],
    ([MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT] + isnull(sum([MKTSEGMENTATIONTESTSEGMENT].[GIFTAMOUNT]),0)) / (1 + count([MKTSEGMENTATIONTESTSEGMENT].[ID])) as [EXPECTEDGIFTAMOUNT],
    (([MKTSEGMENTATIONSEGMENT].[RESPONSERATE] + isnull(sum([MKTSEGMENTATIONTESTSEGMENT].[RESPONSERATE]),0)) / (1 + count([MKTSEGMENTATIONTESTSEGMENT].[ID]))) / 100 as [EXPECTEDRESPONSERATE],
    [MKTSEGMENTATIONSEGMENT].[BASECURRENCYID]
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
  left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
  left join dbo.[MKTPACKAGE] as [TESTPACKAGE] on [TESTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
  where [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = @SEGMENTID
  and [MKTSEGMENTATION].[ACTIVE] = 0
  group by [MKTSEGMENTATION].[ID], [MKTSEGMENTATION].[NAME], [MKTSEGMENTATIONSEGMENT].[ID], [MKTSEGMENTATIONSEGMENT].[SEGMENTID], [MKTSEGMENTATIONSEGMENT].[PACKAGEID], [MKTPACKAGE].[NAME], [MKTPACKAGE].[UNITCOST], [MKTSEGMENTATIONSEGMENT].[SAMPLESIZE], [MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPECODE], [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT], [MKTSEGMENTATIONSEGMENT].[RESPONSERATE], [MKTSEGMENTATIONSEGMENT].[BASECURRENCYID]
  order by [MKTSEGMENTATION].[NAME];

  return 0;