USP_DATALIST_MEMBERSHIPMAILINGRENEWALREPORTDETAIL

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

  select
    [S].[NAME] as [NAME],
    [S].[CODE] as [CODE],
    [SSA].[QUANTITY],
    [SSA].[RESPONSES],
    [MMSA].[RENEWALS],
    [MMSA].[UPGRADES],
    [MMSA].[DOWNGRADES],
    ([MMSA].[RENEWALS] - [MMSA].[UPGRADES] - [MMSA].[DOWNGRADES]) as [SAME],
    -- we store these as percents, but we're dividing them by 100 to get them back to rates to take advantage of 

    -- the report's ability to format a rate as a percentage

    [MMSA].[RENEWALRATE] / 100 as [RENEWALRATE],
    [MMSA].[UPGRADERATE] / 100 as [UPGRADERATE],
    [MMSA].[DOWNGRADERATE] / 100 as [DOWNGRADERATE],
    [SS].[SEGMENTID]
  from dbo.[MKTSEGMENTATIONSEGMENT] as [SS] 
  inner join dbo.[MKTSEGMENT] as [S] on [S].[ID] = [SS].[SEGMENTID]
  left outer join dbo.[MKTSEGMENTATIONTESTSEGMENT] as [STS] on [STS].[SEGMENTID] = [SS].[ID]
  inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] as [SSA] on (
    [SSA].[SEGMENTID] = [SS].[ID]
    and
    (([SSA].[TESTSEGMENTID] is null and [STS].[ID] is null) or [STS].[ID] = [SSA].[TESTSEGMENTID]))
  inner join dbo.[MKTMEMBERSHIPMAILINGSEGMENTACTIVE] as [MMSA] on [MMSA].[ID] = [SSA].[ID]
  where [SS].[SEGMENTATIONID] = @SEGMENTATIONID
  order by [SS].[SEQUENCE];

  return 0;