USP_DATALIST_MEMBERSHIPMAILINGRENEWALREPORTSUMMARY

CREATE procedure dbo.[USP_DATALIST_MEMBERSHIPMAILINGRENEWALREPORTSUMMARY]
(
  @SEGMENTATIONID uniqueidentifier,
  @CURRENCYCODE tinyint = 1
)
as
  set nocount on;

  declare @APPEALLIST nvarchar(max);
  declare @APPEAL nvarchar(100);
  declare APPEALCURSOR cursor local fast_forward for

  select [APPEALDESCRIPTION]
  from dbo.[MKTSEGMENTATIONACTIVATE]
  where [SEGMENTATIONID] = @SEGMENTATIONID;

  open APPEALCURSOR;
  fetch next from APPEALCURSOR into @APPEAL;

  while (@@FETCH_STATUS = 0)
  begin
    if @APPEALLIST is not null
      set @APPEALLIST = @APPEALLIST + ', ';

    set @APPEALLIST = isnull(@APPEALLIST,'') + @APPEAL;

    fetch next from APPEALCURSOR into @APPEAL;
  end;

  close APPEALCURSOR;
  deallocate APPEALCURSOR;

  select
    [MKTSEGMENTATION].[NAME],
    [MKTSEGMENTATION].[DESCRIPTION],
    [MKTSEGMENTATION].[CODE],
    [MKTSEGMENTATION].[IDINTEGER] as [MAILINGID],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEGMENTATION].[SITEID]) as [SITE],
    isnull(@APPEALLIST, '') as [APPEAL],
    [MKTSEGMENTATION].[MAILDATE],
    [MKTSEGMENTATION].[ACTIVATEDATE],
    isnull([MKTSEGMENTATIONREFRESHPROCESS].[DATEREFRESHED], [MKTSEGMENTATION].[ACTIVATEDATE]) as [REFRESHDATE],
    (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [SEGMENTS],
    (select count([ID]) from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [PACKAGES],
    (select count([ID]) from dbo.[MKTSEGMENTATIONSEED] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [SEEDS],
    case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONBUDGET].[ORGANIZATIONBUDGETAMOUNT] else [MKTSEGMENTATIONBUDGET].[BUDGETAMOUNT] end as [BUDGETAMOUNT],
    (select
       case when @CURRENCYCODE = 1 then sum([ORGANIZATIONTOTALCOST]) else sum([TOTALCOST]) end 
     from dbo.[MKTSEGMENTATIONSEGMENTACTIVE]
     inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID]
     where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [EXPENSES],
    [MKTSEGMENTATIONACTIVE].[QUANTITY],
    [MKTSEGMENTATIONACTIVE].[RESPONSES],
    [MKTMEMBERSHIPMAILINGACTIVE].[RENEWALS],
    [MKTMEMBERSHIPMAILINGACTIVE].[UPGRADES],
    [MKTMEMBERSHIPMAILINGACTIVE].[DOWNGRADES],
    ([MKTMEMBERSHIPMAILINGACTIVE].[RENEWALS] - [MKTMEMBERSHIPMAILINGACTIVE].[UPGRADES] - [MKTMEMBERSHIPMAILINGACTIVE].[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

    [MKTMEMBERSHIPMAILINGACTIVE].[RENEWALRATE] / 100 as [RENEWALRATE],
    [MKTMEMBERSHIPMAILINGACTIVE].[UPGRADERATE] / 100 as [UPGRADERATE],
    [MKTMEMBERSHIPMAILINGACTIVE].[DOWNGRADERATE] / 100 as [DOWNGRADERATE],
    [CURRENCY].[ISO4217],
    [CURRENCY].[DECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE]
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATIONBUDGET].[ID] = [MKTSEGMENTATION].[ID]
  inner join dbo.[MKTSEGMENTATIONACTIVE] on [MKTSEGMENTATIONACTIVE].[ID] = [MKTSEGMENTATION].[ID]
  inner join dbo.[MKTMEMBERSHIPMAILINGACTIVE] on [MKTMEMBERSHIPMAILINGACTIVE].[ID] = [MKTSEGMENTATIONACTIVE].[ID]
  left join dbo.[MKTSEGMENTATIONREFRESHPROCESS] on [MKTSEGMENTATIONREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case when @CURRENCYCODE = 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTSEGMENTATIONBUDGET].[BASECURRENCYID] end
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

  return 0;