USP_MKTSEGMENTATIONSEGMENTACTIVE_GETSEGMENTS

CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENTACTIVE_GETSEGMENTS]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

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

  select
    [T].[SEGMENTID],
    [T].[TESTSEGMENTID],
    [T].[SEGMENTTYPECODE],
    [T].[IMPRESSIONS],
    convert(tinyint, [T].[IMPRESSIONCALCULATIONMETHODCODE]) as [IMPRESSIONCALCULATIONMETHODCODE],
    convert(decimal(19,4), [T].[COSTPERPIECE]) as [COSTPERPIECE],
    [T].[RESPONSERATE],
    [T].[COSTPERRESPONSE],
    [T].[FIXEDCOST],
    [T].[GIFTAMOUNT],
    convert(decimal(19,4), [T].[ORGANIZATIONCOSTPERPIECE]) as [ORGANIZATIONCOSTPERPIECE],
    [T].[ORGANIZATIONCOSTPERRESPONSE],
    [T].[ORGANIZATIONFIXEDCOST],
    [T].[ORGANIZATIONGIFTAMOUNT],
    [T].[EXCLUDE]
  from (
    select
      [MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID],
      [MKTSEGMENTATIONSEGMENT].[EXCLUDE],
      null as [TESTSEGMENTID],
      [MKTSEGMENT].[SEGMENTTYPECODE],
      isnull([MKTSEGMENTPASSIVE].[IMPRESSIONS], 0) as [IMPRESSIONS],
      isnull([MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHODCODE], 0) as [IMPRESSIONCALCULATIONMETHODCODE],

      cast(case [MKTSEGMENTATIONSEGMENT].[EXCLUDE] when 1 then 0 else
             case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE]
               when 0 then [MKTSEGMENTATIONPACKAGE].[UNITCOST]
               when 4 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] / @PACKAGEPERTHOUSANDAMOUNT else 0 end +
             [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERPIECE] +
             case when [MKTSEGMENT].[SEGMENTTYPECODE] = 2 then dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD2]([MKTSEGMENTATIONSEGMENT].[ID], 0) else 0 end
      end as decimal(30,8)) as [COSTPERPIECE],

      [MKTSEGMENTATIONSEGMENT].[RESPONSERATE],

      case [MKTSEGMENTATIONSEGMENT].[EXCLUDE] when 1 then 0 else
        case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 1 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] else 0 end +
        [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERRESPONSE]
      end as [COSTPERRESPONSE],

      case [MKTSEGMENTATIONSEGMENT].[EXCLUDE] when 1 then 0 else
        -- 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 [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 2 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] else 0 end +
         [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPEREFFORT])
        /
        ((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))
      end as [FIXEDCOST],

      [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT],
      [MKTSEGMENTATIONSEGMENT].[SEQUENCE] as [SEGSEQ],
      0 as [TESTSEQ],

      cast(case [MKTSEGMENTATIONSEGMENT].[EXCLUDE] when 1 then 0 else
             case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
               when 0 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST]
               when 4 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST]/@PACKAGEPERTHOUSANDAMOUNT else 0 end +
             [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPERPIECE] +
             case when [MKTSEGMENT].[SEGMENTTYPECODE] = 2 then dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD2]([MKTSEGMENTATIONSEGMENT].[ID], 1) else 0 end
      end as decimal(30,8)) as [ORGANIZATIONCOSTPERPIECE],

      case [MKTSEGMENTATIONSEGMENT].[EXCLUDE] when 1 then 0 else
        case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else 0 end +
        [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPERRESPONSE]
      end as [ORGANIZATIONCOSTPERRESPONSE],

      case [MKTSEGMENTATIONSEGMENT].[EXCLUDE] when 1 then 0 else
        (case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 2 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else 0 end +
         [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPEREFFORT])
        /
        ((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))
      end as [ORGANIZATIONFIXEDCOST],

      [MKTSEGMENTATIONSEGMENT].[ORGANIZATIONGIFTAMOUNT]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left outer join dbo.[MKTSEGMENTPASSIVE] on [MKTSEGMENTPASSIVE].[ID] = [MKTSEGMENT].[ID]
    left outer join dbo.[MKTSEGMENTATIONPACKAGE] on ([MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0 and [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID])
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID

    union

    select
      [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID],
      [MKTSEGMENTATIONSEGMENT].[EXCLUDE],
      [MKTSEGMENTATIONTESTSEGMENT].[ID] as [TESTSEGMENTID],
      [MKTSEGMENT].[SEGMENTTYPECODE],
      0 as [IMPRESSIONS],
      0 as [IMPRESSIONCALCULATIONMETHODCODE],

      case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
        when 0 then [MKTSEGMENTATIONPACKAGE].[UNITCOST]
        when 4 then [MKTSEGMENTATIONPACKAGE].[UNITCOST]/@PACKAGEPERTHOUSANDAMOUNT else 0 end +
      [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERPIECE] +
      case when [MKTSEGMENT].[SEGMENTTYPECODE] = 2 then dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD2]([MKTSEGMENTATIONSEGMENT].[ID], 0) else 0 end
      as [COSTPERPIECE],

      [MKTSEGMENTATIONTESTSEGMENT].[RESPONSERATE],

      case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 1 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] else 0 end +
      [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERRESPONSE]
      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 [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 2 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] else 0 end +
       [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPEREFFORT])
      /
      ((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 [FIXEDCOST],

      [MKTSEGMENTATIONTESTSEGMENT].[GIFTAMOUNT],
      [MKTSEGMENTATIONSEGMENT].[SEQUENCE] as [SEGSEQ],
      [MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE] as [TESTSEQ],

      case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
        when 0 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST]
        when 4 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST]/@PACKAGEPERTHOUSANDAMOUNT else 0 end +
      [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPERPIECE] +
      case when [MKTSEGMENT].[SEGMENTTYPECODE] = 2 then dbo.[UFN_MKTSEGMENTATIONSEGMENTLIST_GETCOSTPERRECORD2]([MKTSEGMENTATIONSEGMENT].[ID], 1) else 0 end
      as [ORGANIZATIONCOSTPERPIECE],

      case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else 0 end +
      [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPERRESPONSE]
      as [ORGANIZATIONCOSTPERRESPONSE],

      -- 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 [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 2 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else 0 end +
       [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPEREFFORT])
      /
      ((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 [ORGANIZATIONFIXEDCOST],

      [MKTSEGMENTATIONTESTSEGMENT].[ORGANIZATIONGIFTAMOUNT]
    from dbo.[MKTSEGMENTATIONTESTSEGMENT]
    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    inner join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
  ) as [T]
  order by [T].[SEGSEQ], [T].[TESTSEQ];

  return 0;