USP_DATALIST_MEMBERSHIPPROGRAM_REVENUETRENDBYLEVEL

            create procedure dbo.USP_DATALIST_MEMBERSHIPPROGRAM_REVENUETRENDBYLEVEL
            (
                @PROGRAMID uniqueidentifier,
                @FROMDATE datetime = null,
                @TODATE datetime = null,
                @LEVEL1NAME nvarchar(100) = null output,
                @LEVEL2NAME nvarchar(100) = null output,
                @LEVEL3NAME nvarchar(100) = null output,
                @LEVEL4NAME nvarchar(100) = null output,
                @LEVEL5NAME nvarchar(100) = null output,
                @LEVEL6NAME nvarchar(100) = null output
            )
            as
                set nocount on;

                set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE);
                set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);

                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                declare @SELECTEDCURRENCYID uniqueidentifier;

                set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;


                declare @DECIMALDIGITS tinyint;
                declare @ROUNDINGTYPECODE tinyint;
                declare @CURRENCYISO nvarchar(6);
                declare @CURRENCYSYMBOL nvarchar(10);
                declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

                select
                    @CURRENCYISO = CURRENCY.ISO4217,
                    @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                    @CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
                    @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
                    @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE
                from
                    dbo.CURRENCY
                where
                    CURRENCY.ID = @SELECTEDCURRENCYID;

                declare @LEVEL1ID uniqueidentifier
                declare @LEVEL2ID uniqueidentifier
                declare @LEVEL3ID uniqueidentifier
                declare @LEVEL4ID uniqueidentifier
                declare @LEVEL5ID uniqueidentifier
                declare @LEVEL6ID uniqueidentifier

                declare @MEMBERSHIPREVENUE table(TOTAL money, TRANSACTIONDATE datetime, LEVELID uniqueidentifier)

                insert into @MEMBERSHIPREVENUE
                select
                    sum(TOTAL),
                    TRANSACTIONDATE,
                    LEVELID
                from (
                    select
                        REVENUEAMOUNT.AMOUNTINCURRENCY TOTAL,
                        MEMBERSHIPTRANSACTION.TRANSACTIONDATE TRANSACTIONDATE,
                        MEMBERSHIPLEVEL.ID LEVELID
                    from dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
                        @SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE
                    ) as REVENUEAMOUNT
                    inner join dbo.MEMBERSHIPTRANSACTION 
                        on REVENUEAMOUNT.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                    inner join dbo.MEMBERSHIPLEVEL
                        on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
                    where MEMBERSHIPTRANSACTION.TRANSACTIONDATE between @FROMDATE and @TODATE
                    and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @PROGRAMID

                    union all

                    select
                        -CREDITITEM.TOTAL TOTAL,
                        CREDIT.TRANSACTIONDATE TRANSACTIONDATE,
                        MEMBERSHIPLEVEL.ID LEVELID
                    from dbo.CREDIT
                    inner join dbo.CREDITITEM 
                        on CREDITITEM.CREDITID = CREDIT.ID
                    inner join dbo.CREDITPAYMENT 
                        on CREDITPAYMENT.CREDITID = CREDIT.ID
                    inner join dbo.REVENUESPLIT 
                        on (REVENUESPLIT.REVENUEID = CREDITPAYMENT.REVENUEID and REVENUESPLIT.TYPECODE = 2)
                        or (REVENUESPLIT.ID = CREDITPAYMENT.REVENUESPLITID)
                    inner join dbo.CREDITITEMMEMBERSHIP
                        on CREDITITEM.ID = CREDITITEMMEMBERSHIP.ID
                    inner join dbo.MEMBERSHIPLEVEL
                        on CREDITITEMMEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    where CREDIT.TRANSACTIONDATE between @FROMDATE and @TODATE
                    and CREDIT.TYPECODE = 0
                    and CREDITITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = @PROGRAMID
                ) as RAWMEMBERSHIPREVENUE
                group by LEVELID, TRANSACTIONDATE
                order by TRANSACTIONDATE asc

                declare @LEVELID uniqueidentifier
                declare @LEVELNAME nvarchar(100)
                declare @TOTAL money
                declare @IDX int = 0

                declare TOP_LEVELS cursor local fast_forward for

                select top(6)
                    LEVELID
                from @MEMBERSHIPREVENUE
                group by LEVELID
                order by sum(TOTAL) desc

                open TOP_LEVELS

                fetch next from TOP_LEVELS into @LEVELID

                while @@fetch_status = 0
                begin    

                    set @LEVELNAME = (select NAME from dbo.MEMBERSHIPLEVEL where ID = @LEVELID)

                    if @IDX = 0
                        select 
                            @LEVEL1NAME = @LEVELNAME,
                            @LEVEL1ID = @LEVELID

                    if @IDX = 1
                        select 
                            @LEVEL2NAME = @LEVELNAME,
                            @LEVEL2ID = @LEVELID

                    if @IDX = 2
                        select 
                            @LEVEL3NAME = @LEVELNAME,
                            @LEVEL3ID = @LEVELID

                    if @IDX = 3
                        select 
                            @LEVEL4NAME = @LEVELNAME,
                            @LEVEL4ID = @LEVELID

                    if @IDX = 4
                        select 
                            @LEVEL5NAME = @LEVELNAME,
                            @LEVEL5ID = @LEVELID

                    if @IDX = 5
                        select 
                            @LEVEL6NAME = @LEVELNAME,
                            @LEVEL6ID = @LEVELID

                    set @IDX = @IDX + 1

                    fetch next from TOP_LEVELS into @LEVELID

                end

                select
                    TRANSACTIONDATE,
                    case
                        when LEVELID = @LEVEL1ID then TOTAL
                        else null
                    end as LEVEL1AMOUNT,
                    case
                        when LEVELID = @LEVEL2ID then TOTAL
                        else null
                    end as LEVEL2AMOUNT,
                    case
                        when LEVELID = @LEVEL3ID then TOTAL
                        else null
                    end as LEVEL3AMOUNT,
                    case
                        when LEVELID = @LEVEL4ID then TOTAL
                        else null
                    end as LEVEL4AMOUNT,
                    case
                        when LEVELID = @LEVEL5ID then TOTAL
                        else null
                    end as LEVEL5AMOUNT,
                    case
                        when LEVELID = @LEVEL6ID then TOTAL
                        else null
                    end as LEVEL6AMOUNT
                from @MEMBERSHIPREVENUE
                where LEVELID in (@LEVEL1ID, @LEVEL2ID, @LEVEL3ID, @LEVEL4ID, @LEVEL5ID, @LEVEL6ID)