USP_MKTSEGMENTATIONPASSIVESEGMENT_GETDATALIST

CREATE procedure dbo.[USP_MKTSEGMENTATIONPASSIVESEGMENT_GETDATALIST]
(
  @SEGMENTATIONID uniqueidentifier,
  @CURRENCYCODE tinyint = 0 /* 0 = base, 1 = organization */
)
as
  set nocount on;

  declare @PACKAGEPERTHOUSANDAMOUNT decimal(15,5) = 1000.0;

  select
    [MKTSEGMENTATIONSEGMENT].[ID],
    [MKTSEGMENT].[NAME],
    [SOURCECODE].[SOURCECODE],
    [MKTSEGMENTATIONSEGMENT].[SAMPLESIZE],
    [MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPECODE],
    [MKTSEGMENTATIONSEGMENT].[SAMPLESIZEEXCLUDEREMAINDER],
    [MKTPACKAGE].[NAME] as [PACKAGENAME],
    [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
    datediff(dd, [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE], [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE]) + 1 as [EXPOSURE],
    case [MKTSEGMENTATION].[ACTIVE] when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONS] else [MKTSEGMENTPASSIVE].[IMPRESSIONS] end as [IMPRESSIONS],
    case [MKTSEGMENTATION].[ACTIVE] when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONCALCULATIONMETHODCODE] else [MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHODCODE] end as [IMPRESSIONCALCULATIONMETHODCODE],
    case [MKTSEGMENTATION].[ACTIVE] when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONCALCULATIONMETHOD] else [MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHOD] end as [IMPRESSIONCALCULATIONMETHOD],
    (
      (case [MKTSEGMENTATION].[ACTIVE] 
         when 1 then 
           case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
             when 0 then case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else [MKTSEGMENTATIONPACKAGE].[UNITCOST] end
             when 4 then case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST]/@PACKAGEPERTHOUSANDAMOUNT else [MKTSEGMENTATIONPACKAGE].[UNITCOST]/@PACKAGEPERTHOUSANDAMOUNT end else 0 end +
           case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPERPIECE] else [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERPIECE] end
         else 
           case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
             when 0 then case when @CURRENCYCODE = 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] else [MKTPACKAGE].[UNITCOST] end
             when 4 then case when @CURRENCYCODE = 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST]/@PACKAGEPERTHOUSANDAMOUNT else [MKTPACKAGE].[UNITCOST]/@PACKAGEPERTHOUSANDAMOUNT end else 0 end +
           dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 0, @CURRENCYCODE) +
           -- Add any other package costs using 'Per thousand'

           dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 4, @CURRENCYCODE)
       end)
      + 
      (case [MKTSEGMENT].[SEGMENTTYPECODE] 
         when 2 then 
           dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD2]([MKTSEGMENTATIONSEGMENT].[ID], @CURRENCYCODE) 
         else 0 
       end)
    ) as [COSTPERPIECE],
    (case [MKTSEGMENTATION].[ACTIVE] 
       when 1 then 
         case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
           when 1 then case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else [MKTSEGMENTATIONPACKAGE].[UNITCOST] end else 0 end +
         case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPERRESPONSE] else [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERRESPONSE] end
       else 
         case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
           when 1 then case when @CURRENCYCODE = 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] else [MKTPACKAGE].[UNITCOST] end else 0 end +
         dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 1, @CURRENCYCODE)
     end)
    as [COSTPERRESPONSE],
    -- calculate this segment's share of the package's cost per effort, which is split evenly among all segments in the mailing

    -- using this package, and will be rolled up into the segment's fixed cost

    (
     (case [MKTSEGMENTATION].[ACTIVE] 
       when 1 then 
         case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
           when 2 then case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else [MKTSEGMENTATIONPACKAGE].[UNITCOST] end else 0 end +
         case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPEREFFORT] else [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPEREFFORT] end
       else 
         case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
           when 2 then case when @CURRENCYCODE = 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] else [MKTPACKAGE].[UNITCOST] end else 0 end +
         dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 2, @CURRENCYCODE)
     end)
     /
    ((select count([SS].[ID]) from dbo.[MKTSEGMENTATIONSEGMENT] as [SS] where [SS].[SEGMENTATIONID] = @SEGMENTATIONID and [SS].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]) +
     isnull((select count([SS].[ID]) from dbo.[MKTSEGMENTATIONTESTSEGMENT] as [STS] inner join dbo.[MKTSEGMENTATIONSEGMENT] as [SS] on [SS].[ID] = [STS].[SEGMENTID] where [SS].[SEGMENTATIONID] = @SEGMENTATIONID and [STS].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]), 0))
    ) as [FIXEDCOSTFROMPACKAGE],
    [MKTSEGMENTATIONSEGMENT].[RESPONSERATE],
    case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONSEGMENT].[ORGANIZATIONGIFTAMOUNT] else [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT] end as [GIFTAMOUNT],
    [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
    [MKTSEGMENT].[SEGMENTTYPECODE],
    [MKTSEGMENTATIONSEGMENT].[BASECURRENCYID],
    [CURRENCY].[ISO4217],
    [CURRENCY].[DECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE]
  from dbo.[MKTSEGMENTATIONSEGMENT]
  left outer join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENTPASSIVE] on [MKTSEGMENTPASSIVE].[ID] = [MKTSEGMENT].[ID]
  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
  inner join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case when @CURRENCYCODE = 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTSEGMENTATION].[BASECURRENCYID] end
  left join dbo.[UFN_MKTSOURCECODE_BUILDCODESFORSEGMENTATION](@SEGMENTATIONID, null, 0) as [SOURCECODE] on [SOURCECODE].[SEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
  where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
  and [MKTSEGMENTATION].[MAILINGTYPECODE] = 4 -- so this can be used with reports that don't differentiate

  order by [MKTSEGMENT].[NAME];

  return 0;