USP_DATALIST_MEMBERSHIPSTATISTICSCOMPARISONREPORT
Fetches membership statistics comparison data.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @THISSTARTDATE | datetime | IN | Start Date |
| @THISENDDATE | datetime | IN | End Date |
| @LASTSTARTDATE | datetime | IN | Start Date |
| @LASTENDDATE | datetime | IN | End Date |
| @GROUPBY | tinyint | IN | Group by |
| @MEMBERSHIPPROGRAMID | uniqueidentifier | IN | Membership Program |
| @CURRENCYCODE | tinyint | IN | Currency |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_MEMBERSHIPSTATISTICSCOMPARISONREPORT
(
@THISSTARTDATE datetime,
@THISENDDATE datetime,
@LASTSTARTDATE datetime,
@LASTENDDATE datetime,
@GROUPBY tinyint,
@MEMBERSHIPPROGRAMID uniqueidentifier,
@CURRENCYCODE tinyint
)
as
/*
WI #116688 (11/01/2010) the definition of "retention" in this stored proc has been wildly different than the definition
in the retention KPI. In order to harmonize the 2, retention rate will now be defined as the percentage of previous
period members (as of last end date) who are still members as of this end date *and* have renewed at least once since
the last end date. This must always be a real number between 0 and 100, inclusive. The actual SQL statements are slightly different
than those in the KPI, though, in order to optimize processing.
*/
/*
11/07/2010 ChrisFal further changes:
- joins to MEMBER table in this sproc and functions it calls are eliminated, bc/ this sproc evaluates memberships not members. It's in the title.
- no longer do LATESTTIME/EARLIEST time on dates passed by caller, since the caller is responsible for doing so
- uses >= START and < END rather than "between START and END" due to the way caller transforms user-entered end date.
(Caller sets END to 00:00:00 the day after.)
- performance optimization by joining to inline TVFs rather than calling scalar functions on each row
*/
/*
11/16/2010 ChrisFal further changes:
- per Megan Williams, the breakdown stats (by activity or channel code) should include *all* transactions within the period, regardless of how
many times a membership might have had a transaction. For example, if a membership is dropped in January this year, then rejoined in July, this
year's stats would tally both transactions. However, the total memberships as of year end would show only one membership. (This means that columns
do not necessarily sum in the report.)
- Another perf optimization: removed non-varying data (such as currency decimal digits) from table var @MEMBERSHIPTRANSACTIONSANDAMOUNT,
which also simplifies subsequent group by clauses
*/
/*
3/1/2012 KellySu:
- include the membership add-on revenue and the installment plan payment revenue.
- include the membership recurring payment revenue.
*/
set nocount on;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @SELECTEDCURRENCYID uniqueidentifier;
if coalesce(@CURRENCYCODE, 1) = 1
begin
set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;
end
else
begin
select
@SELECTEDCURRENCYID = MEMBERSHIPPROGRAM.BASECURRENCYID
from
dbo.MEMBERSHIPPROGRAM
where
MEMBERSHIPPROGRAM.ID = @MEMBERSHIPPROGRAMID;
end
declare @DECIMALDIGITS tinyint;
declare @ROUNDINGTYPECODE tinyint;
declare @CURRENCYISO nvarchar(6);
declare @CURRENCYSYMBOL nvarchar(10);
declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
select
@DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
@ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
@CURRENCYISO = CURRENCY.ISO4217,
@CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE
from
dbo.CURRENCY
where
CURRENCY.ID = @SELECTEDCURRENCYID;
-- ChrisFal 11/4/2010 - removed calls to date GETEARLIEST and GETLATEST functions bc/they cause an off-by-one error, since the
-- report already performs this math on the dates (though in a slightly different way).
declare @MEMBERSHIPTRANSACTIONSANDAMOUNT table
(
TIER uniqueidentifier,
TIERSEQUENCE int,
MEMBERSHIPLEVEL uniqueidentifier,
MEMBERSHIPLEVELSEQUENCE tinyint,
MEMBERSHIPTRANSACTIONID uniqueidentifier,
REVENUEAMOUNT money,
ACTIONCODE tinyint,
CHANNELCODEID uniqueidentifier,
PERIOD bit,
REFUNDAMOUNT money
);
/* Begin populate @MEMBERSHIPTRANSACTIONSANDAMOUNT */
with MEMBERSHIPDISCOUNT_CTE as
(
select
REVENUESPLITID,
sum(CREDITITEM.TOTAL) as CREDITAMOUNT
from
dbo.CREDITITEM
inner join dbo.CREDIT on CREDITITEM.CREDITID = CREDIT.ID
where
CREDIT.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID is not null
and REVENUESPLITID is not null
and CREDITITEM.TYPECODE = 1
group by
REVENUESPLITID
),
MEMBERSHIPREFUND_CTE_2 as
(
select
REVENUESPLITID,
sum(CREDITITEM.TOTAL) as CREDITAMOUNT
from
dbo.CREDITITEM
inner join dbo.CREDIT on CREDITITEM.CREDITID = CREDIT.ID
where
CREDIT.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID is null
and REVENUESPLITID is not null
and CREDITITEM.TYPECODE IN (1,16)
group by
REVENUESPLITID
)
-- Period: 1 = this period, 0 = last period.
-- All downgrades should exist within the previous level.
-- If a downgrade is found, the level in the transaction previous is stored.
insert into @MEMBERSHIPTRANSACTIONSANDAMOUNT
(
TIER,
TIERSEQUENCE,
MEMBERSHIPLEVEL,
MEMBERSHIPLEVELSEQUENCE,
MEMBERSHIPTRANSACTIONID,
REVENUEAMOUNT,
ACTIONCODE,
CHANNELCODEID,
PERIOD,
REFUNDAMOUNT
)
-- Getting all transactions within this period.
select
MEMBERSHIPLEVEL.TIERCODEID,
TIERCODE.SEQUENCE,
MEMBERSHIPLEVEL.ID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPTRANSACTION.ID as MEMBERSHIPTRANSACTIONID,
coalesce(RS.AMOUNTINCURRENCY,0) - coalesce(MEMBERSHIPDISCOUNT_CTE.CREDITAMOUNT, 0) - coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0) as AMOUNT,
MEMBERSHIPTRANSACTION.ACTIONCODE,
RS.CHANNELCODEID,
1,
-coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0)
from
dbo.MEMBERSHIPLEVEL
inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as RS
on MEMBERSHIPTRANSACTION.REVENUESPLITID = RS.ID
and RS.APPLICATIONCODE = 5
and RS.TRANSACTIONTYPECODE in (0,5)
left outer join MEMBERSHIPREFUND_CTE_2 on RS.ID = MEMBERSHIPREFUND_CTE_2.REVENUESPLITID
left outer join MEMBERSHIPDISCOUNT_CTE on RS.ID = MEMBERSHIPDISCOUNT_CTE.REVENUESPLITID
left outer join dbo.TIERCODE on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
where
(
(RS.DATE >= @THISSTARTDATE and RS.DATE < @THISENDDATE)
or
(
MEMBERSHIPTRANSACTION.TRANSACTIONDATE >= @THISSTARTDATE
and MEMBERSHIPTRANSACTION.TRANSACTIONDATE < @THISENDDATE
and MEMBERSHIPTRANSACTION.ACTIONCODE = 4
)
)
and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
union all
--get the addon revenue this period
select
MEMBERSHIPLEVEL.TIERCODEID,
TIERCODE.SEQUENCE,
MEMBERSHIPLEVEL.ID,
MEMBERSHIPLEVEL.SEQUENCE,
ADDON.MEMBERSHIPTRANSACTIONID,
coalesce(RS.AMOUNTINCURRENCY,0) - coalesce(MEMBERSHIPDISCOUNT_CTE.CREDITAMOUNT, 0) - coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0) as AMOUNT,
ADDON.ACTIONCODE,
RS.CHANNELCODEID,
1,
-coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0)
from
dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as RS
--Addon revenue pulls in transaction info from the membership directly
inner join
(
select
MEMBERSHIPADDON.REVENUESPLITID,
MEMBERSHIP.MEMBERSHIPLEVELID,
MEMBERSHIPTRANSACTION.ID,
MEMBERSHIPTRANSACTION.ACTIONCODE,
MEMBERSHIPTRANSACTION.ACTION,
MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID
from
dbo.MEMBERSHIPADDON
inner join dbo.MEMBERSHIP on MEMBERSHIPADDON.MEMBERSHIPID = MEMBERSHIP.ID
inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
) as ADDON
on ADDON.REVENUESPLITID = RS.ID
and RS.APPLICATIONCODE = 18
and RS.TRANSACTIONTYPECODE in (0,5)
inner join dbo.MEMBERSHIPLEVEL
on MEMBERSHIPLEVEL.ID = ADDON.MEMBERSHIPLEVELID
and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and RS.DATE >= @THISSTARTDATE
and RS.DATE < @THISENDDATE
left outer join MEMBERSHIPREFUND_CTE_2 on RS.ID = MEMBERSHIPREFUND_CTE_2.REVENUESPLITID
left outer join MEMBERSHIPDISCOUNT_CTE on RS.ID = MEMBERSHIPDISCOUNT_CTE.REVENUESPLITID
left outer join dbo.TIERCODE on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
union all
--Get installment payments and installment payment add-ons within this period
select distinct
MEMBERSHIPLEVEL.TIERCODEID,
TIERCODE.SEQUENCE,
MEMBERSHIPLEVEL.ID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPTRANSACTION.ID as MEMBERSHIPTRANSACTIONID,
coalesce(RS.AMOUNTINCURRENCY,0) - coalesce(MEMBERSHIPDISCOUNT_CTE.CREDITAMOUNT, 0) - coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0) as AMOUNT,
MEMBERSHIPTRANSACTION.ACTIONCODE,
RS.CHANNELCODEID,
1,
-coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0)
from
dbo.INSTALLMENTPAYMENT
inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as RS
on RS.ID = INSTALLMENTPAYMENT.PAYMENTID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = RS.ID
inner join dbo.REVENUE_EXT as PLEDGE_REVENUE on PLEDGE_REVENUE.ID = INSTALLMENTPAYMENT.PLEDGEID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as PLEDGE_FTLI on PLEDGE_REVENUE.ID = PLEDGE_FTLI.FINANCIALTRANSACTIONID
inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = PLEDGE_FTLI.ID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
left outer join MEMBERSHIPREFUND_CTE_2 on RS.ID = MEMBERSHIPREFUND_CTE_2.REVENUESPLITID
left outer join MEMBERSHIPDISCOUNT_CTE on RS.ID = MEMBERSHIPDISCOUNT_CTE.REVENUESPLITID
left outer join dbo.TIERCODE on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
where
RS.DATE >= @THISSTARTDATE
and RS.DATE < @THISENDDATE
and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and RS.TRANSACTIONTYPECODE = 0 and REVENUESPLIT_EXT.TYPECODE IN (2, 18) AND RS.APPLICATIONCODE in (18,19)
union all
--Get recurring payments
select
MEMBERSHIPLEVEL.TIERCODEID,
TIERCODE.SEQUENCE,
MEMBERSHIPLEVEL.ID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPTRANSACTION.ID as MEMBERSHIPTRANSACTIONID,
coalesce(RS.AMOUNTINCURRENCY,0) - coalesce(MEMBERSHIPDISCOUNT_CTE.CREDITAMOUNT, 0) - coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0) as AMOUNT,
MEMBERSHIPTRANSACTION.ACTIONCODE,
RS.CHANNELCODEID,
1,
-coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0)
from
dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as RS
inner join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on RS.REVENUEID = RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID
inner join dbo.RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
inner join dbo.FINANCIALTRANSACTION as PLEDGE_XACTION on PLEDGE_XACTION.ID = RECURRINGGIFTINSTALLMENT.REVENUEID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as PLEDGE_FTLI on PLEDGE_FTLI.FINANCIALTRANSACTIONID = PLEDGE_XACTION.ID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = RS.ID
left join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = PLEDGE_FTLI.ID
left join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
-- for donation refund
left outer join MEMBERSHIPREFUND_CTE_2 on RS.ID = MEMBERSHIPREFUND_CTE_2.REVENUESPLITID
left outer join MEMBERSHIPDISCOUNT_CTE on RS.ID = MEMBERSHIPDISCOUNT_CTE.REVENUESPLITID
left outer join dbo.TIERCODE on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
where
RS.DATE >= @THISSTARTDATE
and RS.DATE < @THISENDDATE
and RS.TRANSACTIONTYPECODE = 0 and REVENUESPLIT_EXT.TYPECODE IN (2) and RS.APPLICATIONCODE = 3
and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
union all
-- Getting all transactions within last period
select
MEMBERSHIPLEVEL.TIERCODEID,
TIERCODE.SEQUENCE,
MEMBERSHIPLEVEL.ID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPTRANSACTION.ID as MEMBERSHIPTRANSACTIONID,
coalesce(RS.AMOUNTINCURRENCY,0) - coalesce(MEMBERSHIPDISCOUNT_CTE.CREDITAMOUNT, 0) - coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0) as AMOUNT,
MEMBERSHIPTRANSACTION.ACTIONCODE,
RS.CHANNELCODEID,
0,
-coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0)
from
dbo.MEMBERSHIPLEVEL
inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as RS
on MEMBERSHIPTRANSACTION.REVENUESPLITID = RS.ID
and RS.APPLICATIONCODE in (5,18)
and RS.TRANSACTIONTYPECODE IN (0,5)
left outer join MEMBERSHIPREFUND_CTE_2 on RS.ID = MEMBERSHIPREFUND_CTE_2.REVENUESPLITID
left outer join MEMBERSHIPDISCOUNT_CTE on RS.ID = MEMBERSHIPDISCOUNT_CTE.REVENUESPLITID
left outer join dbo.TIERCODE on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
where
RS.DATE >= @LASTSTARTDATE
and RS.DATE < @LASTENDDATE
and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
union all
select
MEMBERSHIPLEVEL.TIERCODEID,
TIERCODE.SEQUENCE,
MEMBERSHIPLEVEL.ID,
MEMBERSHIPLEVEL.SEQUENCE,
ADDON.MEMBERSHIPTRANSACTIONID,
coalesce(RS.AMOUNTINCURRENCY,0) - coalesce(MEMBERSHIPDISCOUNT_CTE.CREDITAMOUNT, 0) - coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0) as AMOUNT,
ADDON.ACTIONCODE,
RS.CHANNELCODEID,
0,
-coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0)
from
dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as RS
--Addon revenue pulls in transaction info from the membership directly
inner join
(
select
MEMBERSHIPADDON.REVENUESPLITID,
MEMBERSHIP.MEMBERSHIPLEVELID,
MEMBERSHIPTRANSACTION.ID,
MEMBERSHIPTRANSACTION.ACTIONCODE,
MEMBERSHIPTRANSACTION.ACTION,
MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID
from
dbo.MEMBERSHIPADDON
inner join dbo.MEMBERSHIP on MEMBERSHIPADDON.MEMBERSHIPID = MEMBERSHIP.ID
inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
) as ADDON
on ADDON.REVENUESPLITID = RS.ID
and RS.APPLICATIONCODE = 18
and RS.TRANSACTIONTYPECODE IN (0,5)
inner join dbo.MEMBERSHIPLEVEL
on MEMBERSHIPLEVEL.ID = ADDON.MEMBERSHIPLEVELID
and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and RS.DATE >= @LASTSTARTDATE
and RS.DATE < @LASTENDDATE
left outer join MEMBERSHIPREFUND_CTE_2 on RS.ID = MEMBERSHIPREFUND_CTE_2.REVENUESPLITID
left outer join MEMBERSHIPDISCOUNT_CTE on RS.ID = MEMBERSHIPDISCOUNT_CTE.REVENUESPLITID
left outer join dbo.TIERCODE on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
union all
--Get installment payments and installment payment add-ons within last period
select distinct
MEMBERSHIPLEVEL.TIERCODEID,
TIERCODE.SEQUENCE,
MEMBERSHIPLEVEL.ID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPTRANSACTION.ID as MEMBERSHIPTRANSACTIONID,
coalesce(RS.AMOUNTINCURRENCY,0) - coalesce(MEMBERSHIPDISCOUNT_CTE.CREDITAMOUNT, 0) - coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0) as AMOUNT,
MEMBERSHIPTRANSACTION.ACTIONCODE,
RS.CHANNELCODEID,
0,
-coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0)
from
dbo.INSTALLMENTPAYMENT
inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as RS
on RS.ID = INSTALLMENTPAYMENT.PAYMENTID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = RS.ID
inner join dbo.REVENUE_EXT as PLEDGE_REVENUE on PLEDGE_REVENUE.ID = INSTALLMENTPAYMENT.PLEDGEID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as PLEDGE_FTLI on PLEDGE_REVENUE.ID = PLEDGE_FTLI.FINANCIALTRANSACTIONID
inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = PLEDGE_FTLI.ID
inner join dbo.MEMBERSHIPLEVEL ON MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
-- for donation refund
left outer join MEMBERSHIPREFUND_CTE_2 on RS.ID = MEMBERSHIPREFUND_CTE_2.REVENUESPLITID
left outer join MEMBERSHIPDISCOUNT_CTE on RS.ID = MEMBERSHIPDISCOUNT_CTE.REVENUESPLITID
left outer join dbo.TIERCODE on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
where
RS.DATE >= @LASTSTARTDATE
and RS.DATE < @LASTENDDATE
and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
and RS.TRANSACTIONTYPECODE = 0 and REVENUESPLIT_EXT.TYPECODE IN (2, 18) AND RS.APPLICATIONCODE in (18,19)
union all
--Get recurring payments
select
MEMBERSHIPLEVEL.TIERCODEID,
TIERCODE.SEQUENCE,
MEMBERSHIPLEVEL.ID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPTRANSACTION.ID as MEMBERSHIPTRANSACTIONID,
coalesce(RS.AMOUNTINCURRENCY,0) - coalesce(MEMBERSHIPDISCOUNT_CTE.CREDITAMOUNT, 0) - coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0) as AMOUNT,
MEMBERSHIPTRANSACTION.ACTIONCODE,
RS.CHANNELCODEID,
0,
-coalesce(MEMBERSHIPREFUND_CTE_2.CREDITAMOUNT, 0)
from
dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as RS
inner join RECURRINGGIFTINSTALLMENTPAYMENT on RS.REVENUEID = RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID
inner join dbo.RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
inner join dbo.FINANCIALTRANSACTION as PLEDGE_XACTION on PLEDGE_XACTION.ID = RECURRINGGIFTINSTALLMENT.REVENUEID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as PLEDGE_FTLI on PLEDGE_FTLI.FINANCIALTRANSACTIONID = PLEDGE_XACTION.ID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = RS.ID
left join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = PLEDGE_FTLI.ID
left join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
-- for donation refund
left outer join MEMBERSHIPREFUND_CTE_2 on RS.ID = MEMBERSHIPREFUND_CTE_2.REVENUESPLITID
left outer join MEMBERSHIPDISCOUNT_CTE on RS.ID = MEMBERSHIPDISCOUNT_CTE.REVENUESPLITID
left outer join dbo.TIERCODE on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
where RS.DATE >= @LASTSTARTDATE
and RS.DATE < @LASTENDDATE
and RS.TRANSACTIONTYPECODE = 0 and REVENUESPLIT_EXT.TYPECODE IN (2) and RS.APPLICATIONCODE = 3
and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID;
delete
from @MEMBERSHIPTRANSACTIONSANDAMOUNT
where exists
(
select 1
from
dbo.MEMBERSHIPTRANSACTION
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
where
MEMBERSHIPTRANSACTION.ID = [@MEMBERSHIPTRANSACTIONSANDAMOUNT].MEMBERSHIPTRANSACTIONID
and STATUSCODE = 2
);
/* End populate @MEMBERSHIPTRANSACTIONSANDAMOUNT */
-- Calculate totals
declare @TOTALMEMBERSLASTPERIOD integer;
declare @TOTALMEMBERSTHISPERIOD integer;
declare @TOTALJOINEDMEMBERS integer;
declare @TOTALRENEWEDMEMBERS integer;
declare @TOTALRETENTIONRATE decimal(13,4);
select @TOTALMEMBERSLASTPERIOD = count(MEMBERSHIPID) from dbo.UFN_MEMBERSHIPPROGRAM_MEMBERSHIPSASOF(@MEMBERSHIPPROGRAMID, @LASTENDDATE) where ACTIONCODE <> 4
select @TOTALMEMBERSTHISPERIOD = count(MEMBERSHIPID) from dbo.UFN_MEMBERSHIPPROGRAM_MEMBERSHIPSASOF(@MEMBERSHIPPROGRAMID, @THISENDDATE) where ACTIONCODE <> 4
select
@TOTALJOINEDMEMBERS = sum(dbo.UFN_MEMBERSHIPTRANSACTION_MEMBERCOUNT_BYLEVELANDTRANSACTION(MEMBERSHIPLEVEL.ID, @THISSTARTDATE, @THISENDDATE, 0)),
@TOTALRENEWEDMEMBERS = sum(dbo.UFN_MEMBERSHIPTRANSACTION_MEMBERCOUNT_BYLEVELANDTRANSACTION(MEMBERSHIPLEVEL.ID, @THISSTARTDATE, @THISENDDATE, 1))
from
dbo.MEMBERSHIPLEVEL
where
MEMBERSHIPLEVEL.ID in (select distinct MEMBERSHIPLEVEL from @MEMBERSHIPTRANSACTIONSANDAMOUNT);
declare @numPreviousMembers int;
declare @numRetainedMembers int;
with PREVIOUSMEMBERS_CTE as
(
select
MEMBERSHIPID
from
dbo.UFN_MEMBERSHIPPROGRAM_MEMBERSHIPSASOF(@MEMBERSHIPPROGRAMID, @LASTENDDATE)
where
EXPIRATIONDATE < @THISENDDATE -- do not include non-expiring previous memberships (or lifetime memberships)
and ACTIONCODE <> 4 -- do not include those who dropped
),
RETAINEDMEMBERS_CTE as
(
select
MEMBERSHIPID
from
dbo.UFN_MEMBERSHIPPROGRAM_MEMBERSHIPSASOF(@MEMBERSHIPPROGRAMID, @THISENDDATE)
where
ACTIONCODE <> 4 -- do not include those who dropped
)
select
@numPreviousMembers = COUNT(PREV.MEMBERSHIPID),
@numRetainedMembers = COUNT(RET.MEMBERSHIPID)
from
PREVIOUSMEMBERS_CTE PREV
left join RETAINEDMEMBERS_CTE RET on PREV.MEMBERSHIPID = RET.MEMBERSHIPID;
set @TOTALRETENTIONRATE = case @numPreviousMembers
when 0 then convert(decimal(13,4), 0)
else (convert(decimal(13,4), @numRetainedMembers) / convert(decimal(13,4),@numPreviousMembers))
end
-- RobertDi 11/24/2010 - Prepopulate table variables with data to help with performance.
declare @RETENTIONRATES table
(
MEMBERSHIPLEVELID uniqueidentifier,
RETENTIONRATE decimal(13,4)
);
insert into @RETENTIONRATES (MEMBERSHIPLEVELID, RETENTIONRATE)
select
MEMBERSHIPLEVELID,
RETENTIONRATE
from
dbo.UFN_MEMBERSHIPLEVELS_GETRETENTIONRATE(@MEMBERSHIPPROGRAMID, @LASTENDDATE, @THISENDDATE);
declare @THISPERIODMEMBERSHIPS table
(
MEMBERSHIPLEVELID uniqueidentifier,
MEMBERSHIPLEVELSEQUENCE tinyint,
TIER uniqueidentifier,
TIERSEQUENCE int,
MEMBERSHIPCOUNT int
);
declare @LASTPERIODMEMBERSHIPS table
(
MEMBERSHIPLEVELID uniqueidentifier,
MEMBERSHIPLEVELSEQUENCE tinyint,
TIER uniqueidentifier,
TIERSEQUENCE int,
MEMBERSHIPCOUNT int
);
insert into @THISPERIODMEMBERSHIPS
(
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELSEQUENCE,
TIER,
TIERSEQUENCE,
MEMBERSHIPCOUNT
)
select
MEMBERSHIPLEVELID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPLEVEL.TIERCODEID,
TIERCODE.SEQUENCE,
COUNT(MEMBERSHIPID) MEMBERSHIPCOUNT
from
dbo.UFN_MEMBERSHIPPROGRAM_MEMBERSHIPSASOF(@MEMBERSHIPPROGRAMID, @THISENDDATE) MEMBERSHIPS
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPS.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
left outer join dbo.TIERCODE on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
where
MEMBERSHIPS.ACTIONCODE <> 4
group by
MEMBERSHIPS.MEMBERSHIPLEVELID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPLEVEL.TIERCODEID,
TIERCODE.SEQUENCE;
insert into @LASTPERIODMEMBERSHIPS
(
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELSEQUENCE,
TIER,
TIERSEQUENCE,
MEMBERSHIPCOUNT
)
select
MEMBERSHIPLEVELID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPLEVEL.TIERCODEID,
TIERCODE.SEQUENCE,
COUNT(MEMBERSHIPID) MEMBERSHIPCOUNT
from
dbo.UFN_MEMBERSHIPPROGRAM_MEMBERSHIPSASOF(@MEMBERSHIPPROGRAMID, @LASTENDDATE) MEMBERSHIPS
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPS.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
left outer join dbo.TIERCODE on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
where
MEMBERSHIPS.ACTIONCODE <> 4
group by
MEMBERSHIPS.MEMBERSHIPLEVELID,
MEMBERSHIPLEVEL.SEQUENCE,
MEMBERSHIPLEVEL.TIERCODEID,
TIERCODE.SEQUENCE;
-- Group by transaction type.
if @GROUPBY=1
begin
select
dbo.UFN_TIERCODE_GETDESCRIPTION(coalesce(MTA.TIER, THISPERIODMEMBERSHIPS.TIER, LASTPERIODMEMBERSHIPS.TIER)) as TIER,
dbo.UFN_MEMBERSHIPLEVEL_GETNAME(coalesce(MTA.MEMBERSHIPLEVEL, THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELID, LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELID)) as MEMBERSHIPLEVEL,
dbo.UFN_MEMBERSHIPTRANSACTION_ACTIONCODE_GETDESCRIPTION(MTA.ACTIONCODE) as LEVELGROUP,
case MTA.ACTIONCODE
when null then 0
else
(
select coalesce(sum(REVENUEAMOUNT), 0)
from @MEMBERSHIPTRANSACTIONSANDAMOUNT as MTA2
where
MTA2.MEMBERSHIPLEVEL = MTA.MEMBERSHIPLEVEL and
MTA2.ACTIONCODE = MTA.ACTIONCODE and
MTA2.PERIOD = 1
)
end as TRANSACTIONREVENUETHISPERIOD,
case MTA.ACTIONCODE
when null then 0
else
(
select coalesce(sum(REVENUEAMOUNT), 0)
from @MEMBERSHIPTRANSACTIONSANDAMOUNT as MTA2
where
MTA2.MEMBERSHIPLEVEL = MTA.MEMBERSHIPLEVEL and
MTA2.ACTIONCODE = MTA.ACTIONCODE and
MTA2.PERIOD = 0
)
end as TRANSACTIONREVENUELASTPERIOD,
coalesce(THISPERIODMEMBERSHIPS.MEMBERSHIPCOUNT, 0) as LEVELMEMBERSTHISPERIOD,
coalesce(LASTPERIODMEMBERSHIPS.MEMBERSHIPCOUNT, 0) as LEVELMEMBERSLASTPERIOD,
(
select
count(distinct MEMBERSHIPTRANSACTIONID)
from
@MEMBERSHIPTRANSACTIONSANDAMOUNT as MTA2
where
MTA2.MEMBERSHIPLEVEL = coalesce(MTA.MEMBERSHIPLEVEL, THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELID, LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELID) and
MTA2.ACTIONCODE = MTA.ACTIONCODE and
MTA2.PERIOD = 1
) as TRANSACTIONMEMBERSTHISPERIOD,
(
select
count(distinct MEMBERSHIPTRANSACTIONID)
from
@MEMBERSHIPTRANSACTIONSANDAMOUNT as MTA2
where
MTA2.MEMBERSHIPLEVEL = coalesce(MTA.MEMBERSHIPLEVEL, THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELID, LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELID) and
MTA2.ACTIONCODE = MTA.ACTIONCODE and
MTA2.PERIOD = 0
) as TRANSACTIONMEMBERSLASTPERIOD,
coalesce([RETENTION].RETENTIONRATE, 0) as LEVELRETENTIONRATE,
@TOTALMEMBERSTHISPERIOD as TOTALMEMBERSTHISPERIOD,
@TOTALMEMBERSLASTPERIOD as TOTALMEMBERSLASTPERIOD,
@TOTALRETENTIONRATE as TOTALRETENTIONRATE,
@CURRENCYSYMBOL as CURRENCYSYMBOL,
@CURRENCYISO as ISOCURRENCYCODE,
@DECIMALDIGITS as DECIMALDIGITS,
@CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
@SELECTEDCURRENCYID as BASECURRENCYID
from
@MEMBERSHIPTRANSACTIONSANDAMOUNT as MTA
inner join MEMBERSHIPLEVEL on MTA.MEMBERSHIPLEVEL = MEMBERSHIPLEVEL.ID
left join @RETENTIONRATES as [RETENTION] on MTA.MEMBERSHIPLEVEL = [RETENTION].MEMBERSHIPLEVELID
full outer join @THISPERIODMEMBERSHIPS as THISPERIODMEMBERSHIPS on THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELID = MTA.MEMBERSHIPLEVEL
full outer join @LASTPERIODMEMBERSHIPS as LASTPERIODMEMBERSHIPS on LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELID = coalesce(MTA.MEMBERSHIPLEVEL, THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELID)
where
MEMBERSHIPLEVEL.OBTAINLEVELCODE <> 1 --don't show contribution based levels
group by
MTA.TIER,
THISPERIODMEMBERSHIPS.TIER,
LASTPERIODMEMBERSHIPS.TIER,
MTA.TIERSEQUENCE,
THISPERIODMEMBERSHIPS.TIERSEQUENCE,
LASTPERIODMEMBERSHIPS.TIERSEQUENCE,
MTA.MEMBERSHIPLEVEL,
THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELID,
LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELID,
MTA.MEMBERSHIPLEVELSEQUENCE,
THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELSEQUENCE,
LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELSEQUENCE,
MTA.ACTIONCODE,
[RETENTION].RETENTIONRATE,
THISPERIODMEMBERSHIPS.MEMBERSHIPCOUNT,
LASTPERIODMEMBERSHIPS.MEMBERSHIPCOUNT
order by
coalesce(MTA.TIERSEQUENCE, THISPERIODMEMBERSHIPS.TIERSEQUENCE, LASTPERIODMEMBERSHIPS.TIERSEQUENCE),
coalesce(MTA.MEMBERSHIPLEVELSEQUENCE, THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELSEQUENCE, LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELSEQUENCE) asc,
LEVELGROUP;
end
else if @GROUPBY=0
begin
select
dbo.UFN_TIERCODE_GETDESCRIPTION(coalesce(MTA.TIER, THISPERIODMEMBERSHIPS.TIER, LASTPERIODMEMBERSHIPS.TIER)) as TIER,
dbo.UFN_MEMBERSHIPLEVEL_GETNAME(coalesce(MTA.MEMBERSHIPLEVEL, THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELID, LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELID)) as MEMBERSHIPLEVEL,
CHANNELCODE.DESCRIPTION as LEVELGROUP,
case MTA.ACTIONCODE
when null then 0
else
(
select coalesce(sum(REVENUEAMOUNT), 0)
from @MEMBERSHIPTRANSACTIONSANDAMOUNT as MTA2
where
MTA2.ACTIONCODE = MTA.ACTIONCODE and
MTA2.MEMBERSHIPLEVEL = MTA.MEMBERSHIPLEVEL and
MTA2.PERIOD = 1 and
(MTA2.CHANNELCODEID = MTA.CHANNELCODEID or
(MTA2.CHANNELCODEID is null and MTA.CHANNELCODEID is null))
)
end as TRANSACTIONREVENUETHISPERIOD,
case MTA.ACTIONCODE
when null then 0
else
(
select coalesce(sum(REVENUEAMOUNT), 0)
from @MEMBERSHIPTRANSACTIONSANDAMOUNT as MTA2
where
MTA2.ACTIONCODE = MTA.ACTIONCODE and
MTA2.MEMBERSHIPLEVEL = MTA.MEMBERSHIPLEVEL and
MTA2.PERIOD = 0 and
(MTA2.CHANNELCODEID = MTA.CHANNELCODEID or
(MTA2.CHANNELCODEID is null and MTA.CHANNELCODEID is null))
)
end as TRANSACTIONREVENUELASTPERIOD,
coalesce(THISPERIODMEMBERSHIPS.MEMBERSHIPCOUNT, 0) as LEVELMEMBERSTHISPERIOD,
coalesce(LASTPERIODMEMBERSHIPS.MEMBERSHIPCOUNT, 0) as LEVELMEMBERSLASTPERIOD,
(
select
count(distinct MEMBERSHIPTRANSACTION.MEMBERSHIPID)
from
dbo.MEMBERSHIPTRANSACTION
left join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
left join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
where
MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = coalesce(MTA.MEMBERSHIPLEVEL, THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELID, LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELID)
and
(
(MTA.CHANNELCODEID is null and REVENUE_EXT.CHANNELCODEID is null)
or (REVENUE_EXT.CHANNELCODEID = MTA.CHANNELCODEID)
)
and MEMBERSHIPTRANSACTION.TRANSACTIONDATE >= @THISSTARTDATE
and MEMBERSHIPTRANSACTION.TRANSACTIONDATE < @THISENDDATE
and MEMBERSHIPTRANSACTION.ACTIONCODE <> 4
) as TRANSACTIONMEMBERSTHISPERIOD,
(
select
count(distinct MEMBERSHIPTRANSACTION.MEMBERSHIPID)
from
dbo.MEMBERSHIPTRANSACTION
left join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
left join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
where
MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = coalesce(MTA.MEMBERSHIPLEVEL, THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELID, LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELID)
and
(
(MTA.CHANNELCODEID is null and REVENUE_EXT.CHANNELCODEID is null)
or (REVENUE_EXT.CHANNELCODEID = MTA.CHANNELCODEID)
)
and MEMBERSHIPTRANSACTION.TRANSACTIONDATE >= @LASTSTARTDATE
and MEMBERSHIPTRANSACTION.TRANSACTIONDATE < @LASTENDDATE
and MEMBERSHIPTRANSACTION.ACTIONCODE <> 4
) as TRANSACTIONMEMBERSLASTPERIOD,
coalesce([RETENTION].RETENTIONRATE, 0) as LEVELRETENTIONRATE,
@TOTALMEMBERSTHISPERIOD as TOTALMEMBERSTHISPERIOD,
@TOTALMEMBERSLASTPERIOD as TOTALMEMBERSLASTPERIOD,
@TOTALRETENTIONRATE as TOTALRETENTIONRATE,
@CURRENCYSYMBOL as CURRENCYSYMBOL,
@CURRENCYISO as ISOCURRENCYCODE,
@DECIMALDIGITS as DECIMALDIGITS,
@CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
@SELECTEDCURRENCYID as BASECURRENCYID
from
@MEMBERSHIPTRANSACTIONSANDAMOUNT as MTA
inner join MEMBERSHIPLEVEL on MTA.MEMBERSHIPLEVEL = MEMBERSHIPLEVEL.ID
left join @RETENTIONRATES as [RETENTION] on MTA.MEMBERSHIPLEVEL = [RETENTION].MEMBERSHIPLEVELID
left join dbo.CHANNELCODE on CHANNELCODE.ID = MTA.CHANNELCODEID
full outer join @THISPERIODMEMBERSHIPS as THISPERIODMEMBERSHIPS on THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELID = MTA.MEMBERSHIPLEVEL
full outer join @LASTPERIODMEMBERSHIPS as LASTPERIODMEMBERSHIPS on LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELID = coalesce(MTA.MEMBERSHIPLEVEL, THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELID)
where
MEMBERSHIPLEVEL.OBTAINLEVELCODE <> 1 --don't show contribution based levels
group by
MTA.TIER,
THISPERIODMEMBERSHIPS.TIER,
LASTPERIODMEMBERSHIPS.TIER,
MTA.TIERSEQUENCE,
THISPERIODMEMBERSHIPS.TIERSEQUENCE,
LASTPERIODMEMBERSHIPS.TIERSEQUENCE,
MTA.MEMBERSHIPLEVEL,
THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELID,
LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELID,
MTA.MEMBERSHIPLEVELSEQUENCE,
THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELSEQUENCE,
LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELSEQUENCE,
MTA.ACTIONCODE,
MTA.CHANNELCODEID,
[RETENTION].RETENTIONRATE,
THISPERIODMEMBERSHIPS.MEMBERSHIPCOUNT,
LASTPERIODMEMBERSHIPS.MEMBERSHIPCOUNT,
CHANNELCODE.DESCRIPTION
order by
coalesce(MTA.TIERSEQUENCE, THISPERIODMEMBERSHIPS.TIERSEQUENCE, LASTPERIODMEMBERSHIPS.TIERSEQUENCE),
coalesce(MTA.MEMBERSHIPLEVELSEQUENCE, THISPERIODMEMBERSHIPS.MEMBERSHIPLEVELSEQUENCE, LASTPERIODMEMBERSHIPS.MEMBERSHIPLEVELSEQUENCE) asc,
LEVELGROUP;
end
return 0;