USP_DATALIST_MEMBERSHIPPROGRAM_REVENUETREND

            create procedure dbo.USP_DATALIST_MEMBERSHIPPROGRAM_REVENUETREND
            (
                @DATETYPE int = null,
                @FROMDATE datetime = null,
                @TODATE datetime = null,
                @PROGRAM1NAME nvarchar(100) = null output,
                @PROGRAM2NAME nvarchar(100) = null output,
                @PROGRAM3NAME nvarchar(100) = null output,
                @PROGRAM4NAME nvarchar(100) = null output,
                @PROGRAM5NAME nvarchar(100) = null output,
                @PROGRAM6NAME nvarchar(100) = null output
            )

            as
                set nocount on;

                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 @PROGRAM1ID uniqueidentifier
                declare @PROGRAM2ID uniqueidentifier
                declare @PROGRAM3ID uniqueidentifier
                declare @PROGRAM4ID uniqueidentifier
                declare @PROGRAM5ID uniqueidentifier
                declare @PROGRAM6ID uniqueidentifier

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

                insert into @MEMBERSHIPREVENUE
                select
                    sum(TOTAL),
                    TRANSACTIONDATE,
                    PROGRAMID
                from (
                    select
                        REVENUEAMOUNT.AMOUNTINCURRENCY TOTAL,
                        MEMBERSHIPTRANSACTION.TRANSACTIONDATE TRANSACTIONDATE,
                        MEMBERSHIPPROGRAM.ID PROGRAMID
                    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
                    inner join dbo.MEMBERSHIPPROGRAM
                        on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
                    where MEMBERSHIPTRANSACTION.TRANSACTIONDATE between @FROMDATE and @TODATE

                    union all

                    select
                        -CREDITITEM.TOTAL TOTAL,
                        CREDIT.TRANSACTIONDATE TRANSACTIONDATE,
                        MEMBERSHIPPROGRAM.ID PROGRAMID
                    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.MEMBERSHIPPROGRAM
                        on CREDITITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                    where CREDIT.TRANSACTIONDATE between @FROMDATE and @TODATE
                    and CREDIT.TYPECODE = 0
                ) as RAWMEMBERSHIPREVENUE
                group by PROGRAMID, TRANSACTIONDATE
                order by TRANSACTIONDATE asc

                declare @PROGRAMID uniqueidentifier
                declare @PROGRAMNAME nvarchar(100)
                declare @TOTAL money
                declare @IDX int = 0

                declare TOP_PROGRAMS cursor local fast_forward for

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

                open TOP_PROGRAMS

                fetch next from TOP_PROGRAMS into @PROGRAMID

                while @@fetch_status = 0
                begin    

                    set @PROGRAMNAME = (select NAME from dbo.MEMBERSHIPPROGRAM where ID = @PROGRAMID)

                    if @IDX = 0
                        select 
                            @PROGRAM1NAME = @PROGRAMNAME,
                            @PROGRAM1ID = @PROGRAMID

                    if @IDX = 1
                        select 
                            @PROGRAM2NAME = @PROGRAMNAME,
                            @PROGRAM2ID = @PROGRAMID

                    if @IDX = 2
                        select 
                            @PROGRAM3NAME = @PROGRAMNAME,
                            @PROGRAM3ID = @PROGRAMID

                    if @IDX = 3
                        select 
                            @PROGRAM4NAME = @PROGRAMNAME,
                            @PROGRAM4ID = @PROGRAMID

                    if @IDX = 4
                        select 
                            @PROGRAM5NAME = @PROGRAMNAME,
                            @PROGRAM5ID = @PROGRAMID

                    if @IDX = 5
                        select 
                            @PROGRAM6NAME = @PROGRAMNAME,
                            @PROGRAM6ID = @PROGRAMID

                    set @IDX = @IDX + 1

                    fetch next from TOP_PROGRAMS into @PROGRAMID

                end

                select
                    TRANSACTIONDATE,
                    case
                        when PROGRAMID = @PROGRAM1ID then TOTAL
                        else null
                    end as PROGRAM1AMOUNT,
                    case
                        when PROGRAMID = @PROGRAM2ID then TOTAL
                        else null
                    end as PROGRAM2AMOUNT,
                    case
                        when PROGRAMID = @PROGRAM3ID then TOTAL
                        else null
                    end as PROGRAM3AMOUNT,
                    case
                        when PROGRAMID = @PROGRAM4ID then TOTAL
                        else null
                    end as PROGRAM4AMOUNT,
                    case
                        when PROGRAMID = @PROGRAM5ID then TOTAL
                        else null
                    end as PROGRAM5AMOUNT,
                    case
                        when PROGRAMID = @PROGRAM6ID then TOTAL
                        else null
                    end as PROGRAM6AMOUNT
                from @MEMBERSHIPREVENUE
                where PROGRAMID in (@PROGRAM1ID, @PROGRAM2ID, @PROGRAM3ID, @PROGRAM4ID, @PROGRAM5ID, @PROGRAM6ID)