USP_DATALIST_MEMBERSHIPREVENUE

create procedure BBDW.USP_DATALIST_MEMBERSHIPREVENUE(
  @CONTEXTID uniqueidentifier, 
  @DATERANGE tinyint, 
  @STARTDATE1DIMID int, 
  @ENDDATE1DIMID int, 
  @STARTDATE2DIMID int, 
  @ENDDATE2DIMID int,
  @DATERANGE1 nvarchar(50) out,
  @DATERANGE2 nvarchar(50) out
  )
as
    set nocount on;

if @DATERANGE = 0 

  begin
    set @DATERANGE1 = (select top 1 [CALENDARMONTHYEARNAME] from BBDW.[DIM_DATE] where [DATEDIMID] = @STARTDATE1DIMID)
    set @DATERANGE2 = (select top 1 [CALENDARMONTHYEARNAME] from BBDW.[DIM_DATE] where [DATEDIMID] = @STARTDATE2DIMID)
  end;

if @DATERANGE = 1

  begin
    set @DATERANGE1 = (select top 1 [CALENDARQUARTERYEARNAME] from BBDW.[DIM_DATE] where [DATEDIMID] = @STARTDATE1DIMID)
    set @DATERANGE2 = (select top 1 [CALENDARQUARTERYEARNAME] from BBDW.[DIM_DATE] where [DATEDIMID] = @STARTDATE2DIMID)
  end;

if @DATERANGE = 2

  begin
    set @DATERANGE1 = left(cast(@STARTDATE1DIMID as char(8)),4)
    set @DATERANGE2 = left(cast(@STARTDATE2DIMID as char(8)),4)
  end;

with 
  [DATERANGE1] as 
      (select 
            REV1.[MEMBERSHIPLEVEL],
            REV1.[MEMBERSHIPLEVELSYSTEMID],
            isnull(sum(REV1.[AMOUNT]), 0) as [TOTALREVENUE]
        from
            (--Membership payments - One-off

            select coalesce(SUM(FR2.REVENUEAPPLICATIONAMOUNT), 0) as AMOUNT, DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL
            from bbdw.FACT_MEMBERSHIPTRANSACTION FMT
            inner join bbdw.DIM_MEMBERSHIPPROGRAM DMP on FMT.MEMBERSHIPPROGRAMDIMID = DMP.MEMBERSHIPPROGRAMDIMID
            inner join bbdw.FACT_REVENUE FR on FMT.REVENUEFACTID = FR.REVENUEFACTID
            inner join bbdw.DIM_REVENUECODE DRC on FR.REVENUECODEDIMID = DRC.REVENUECODEDIMID
            inner join bbdw.FACT_REVENUE FR2 on Fr2.REVENUESEQUENCEID = FR.REVENUESEQUENCEID and FR2.[REVENUEDATEDIMID] >= @STARTDATE1DIMID and FR2.[REVENUEDATEDIMID] <= @ENDDATE1DIMID
            left join bbdw.FACT_MEMBERSHIPREVENUE FMR on FMR.ORIGINALMEMBERSHIPTRANSACTIONFACTID = FMT.MEMBERSHIPTRANSACTIONFACTID and FR2.REVENUEFACTID = FMR.REVENUEFACTID
            where DRC.REVENUETRANSACTIONTYPECODE = 0
                and (DRC.REVENUEAPPLICATIONCODE in (5,18) or (DRC.REVENUEAPPLICATIONCODE = 0 and FMR.MEMBERSHIPREVENUEFACTID is not null))
                and DMP.MEMBERSHIPPROGRAMSYSTEMID = @CONTEXTID
            group by DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL

            union all

            --Membership payments - Recurring gift

            select coalesce(SUM(RGPAYMENT.RECURRINGGIFTINSTALLMENTPAYMENTAMOUNT), 0) as AMOUNT, DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL
            from bbdw.DIM_MEMBERSHIPPROGRAM DMP
            inner join bbdw.FACT_MEMBERSHIPTRANSACTION FMT on FMT.MEMBERSHIPPROGRAMDIMID = DMP.MEMBERSHIPPROGRAMDIMID
            inner join bbdw.FACT_REVENUE FR on FMT.REVENUEFACTID = FR.REVENUEFACTID
            inner join bbdw.DIM_REVENUECODE DRC on FR.REVENUECODEDIMID = DRC.REVENUECODEDIMID
            inner join bbdw.FACT_REVENUE FR2 on Fr2.REVENUESEQUENCEID = FR.REVENUESEQUENCEID
            inner join bbdw.FACT_RECURRINGGIFTINSTALLMENT RGINSTALLMENT on rginstallment.REVENUEFACTID = fr2.REVENUEFACTID
            inner join bbdw.FACT_RECURRINGGIFTINSTALLMENTPAYMENT RGPAYMENT on RGINSTALLMENT.RECURRINGGIFTINSTALLMENTFACTID = RGPAYMENT.RECURRINGGIFTINSTALLMENTFACTID
            inner join bbdw.FACT_REVENUE FR3 on FR3.REVENUEFACTID = RGPAYMENT.REVENUEFACTID and FR3.[REVENUEDATEDIMID] >= @STARTDATE1DIMID and FR3.[REVENUEDATEDIMID] <= @ENDDATE1DIMID
            where DRC.REVENUETRANSACTIONTYPECODE = 2
                and DMP.MEMBERSHIPPROGRAMSYSTEMID = @CONTEXTID
            group by DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL

            union all

            --Membership payments - Recurring gift addons

            select coalesce(SUM(FR2.REVENUEAPPLICATIONAMOUNT), 0) as AMOUNT, DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL
            from BBDW.FACT_MEMBERSHIPADDON MA
            inner join bbdw.FACT_MEMBERSHIPTRANSACTION FMT on MA.MEMBERSHIPTRANSACTIONFACTID = FMT.MEMBERSHIPTRANSACTIONFACTID
            inner join bbdw.DIM_MEMBERSHIPPROGRAM DMP on FMT.MEMBERSHIPPROGRAMDIMID = DMP.MEMBERSHIPPROGRAMDIMID
            inner join bbdw.FACT_REVENUE FR on FMT.REVENUEFACTID = FR.REVENUEFACTID
            inner join bbdw.DIM_REVENUECODE DRC on FR.REVENUECODEDIMID = DRC.REVENUECODEDIMID
            inner join bbdw.FACT_REVENUE FR2 on MA.REVENUEFACTID = FR2.REVENUEFACTID and FR2.[REVENUEDATEDIMID] >= @STARTDATE1DIMID and FR2.[REVENUEDATEDIMID] <= @ENDDATE1DIMID
            where DRC.REVENUETRANSACTIONTYPECODE = 2
                and DMP.MEMBERSHIPPROGRAMSYSTEMID = @CONTEXTID
            group by DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL

            union all

            --Membership payments - Installment plans

            select coalesce(SUM(IPAYMENT.INSTALLMENTPAYMENTAMOUNT), 0) as AMOUNT, DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL
            from bbdw.DIM_MEMBERSHIPPROGRAM DMP
            inner join bbdw.FACT_MEMBERSHIPTRANSACTION FMT on FMT.MEMBERSHIPPROGRAMDIMID = DMP.MEMBERSHIPPROGRAMDIMID
            inner join bbdw.FACT_REVENUE FR on FMT.REVENUEFACTID = FR.REVENUEFACTID
            inner join bbdw.DIM_REVENUECODE DRC on FR.REVENUECODEDIMID = DRC.REVENUECODEDIMID
            inner join bbdw.FACT_REVENUE FR2 on Fr2.REVENUESEQUENCEID = FR.REVENUESEQUENCEID
            inner join bbdw.FACT_INSTALLMENT INSTALLMENT on installment.REVENUEFACTID = fr2.REVENUEFACTID
            inner join bbdw.FACT_INSTALLMENTPAYMENT IPAYMENT on INSTALLMENT.INSTALLMENTFACTID = IPAYMENT.INSTALLMENTFACTID
            inner join bbdw.FACT_REVENUE FR3 on IPAYMENT.REVENUEFACTID = FR3.REVENUEFACTID and FR3.[REVENUEDATEDIMID] >= @STARTDATE1DIMID and FR3.[REVENUEDATEDIMID] <= @ENDDATE1DIMID
            where DRC.REVENUETRANSACTIONTYPECODE = 15
                and DMP.MEMBERSHIPPROGRAMSYSTEMID = @CONTEXTID
            group by DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL) as REV1
        group by REV1.MEMBERSHIPLEVELSYSTEMID, REV1.MEMBERSHIPLEVEL),

 [DATERANGE2]  as
        (select 
            REV2.[MEMBERSHIPLEVEL],
            REV2.[MEMBERSHIPLEVELSYSTEMID],
            isnull(sum(REV2.[AMOUNT]), 0) as [TOTALREVENUE]
        from
            (--Membership payments - One-off

            select coalesce(SUM(FR2.REVENUEAPPLICATIONAMOUNT), 0) as AMOUNT, DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL
            from bbdw.FACT_MEMBERSHIPTRANSACTION FMT
            inner join bbdw.DIM_MEMBERSHIPPROGRAM DMP on FMT.MEMBERSHIPPROGRAMDIMID = DMP.MEMBERSHIPPROGRAMDIMID
            inner join bbdw.FACT_REVENUE FR on FMT.REVENUEFACTID = FR.REVENUEFACTID
            inner join bbdw.DIM_REVENUECODE DRC on FR.REVENUECODEDIMID = DRC.REVENUECODEDIMID
            inner join bbdw.FACT_REVENUE FR2 on Fr2.REVENUESEQUENCEID = FR.REVENUESEQUENCEID and FR2.[REVENUEDATEDIMID] >= @STARTDATE2DIMID and FR2.[REVENUEDATEDIMID] <= @ENDDATE2DIMID
            left join bbdw.FACT_MEMBERSHIPREVENUE FMR on FMR.ORIGINALMEMBERSHIPTRANSACTIONFACTID = FMT.MEMBERSHIPTRANSACTIONFACTID and FR2.REVENUEFACTID = FMR.REVENUEFACTID
            where DRC.REVENUETRANSACTIONTYPECODE = 0
                and (DRC.REVENUEAPPLICATIONCODE in (5,18) or (DRC.REVENUEAPPLICATIONCODE = 0 and FMR.MEMBERSHIPREVENUEFACTID is not null))
                and DMP.MEMBERSHIPPROGRAMSYSTEMID = @CONTEXTID
            group by DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL

            union all

            --Membership payments - Recurring gift

            select coalesce(SUM(RGPAYMENT.RECURRINGGIFTINSTALLMENTPAYMENTAMOUNT), 0) as AMOUNT, DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL
            from bbdw.DIM_MEMBERSHIPPROGRAM DMP
            inner join bbdw.FACT_MEMBERSHIPTRANSACTION FMT on FMT.MEMBERSHIPPROGRAMDIMID = DMP.MEMBERSHIPPROGRAMDIMID
            inner join bbdw.FACT_REVENUE FR on FMT.REVENUEFACTID = FR.REVENUEFACTID
            inner join bbdw.DIM_REVENUECODE DRC on FR.REVENUECODEDIMID = DRC.REVENUECODEDIMID
            inner join bbdw.FACT_REVENUE FR2 on Fr2.REVENUESEQUENCEID = FR.REVENUESEQUENCEID
            inner join bbdw.FACT_RECURRINGGIFTINSTALLMENT RGINSTALLMENT on rginstallment.REVENUEFACTID = fr2.REVENUEFACTID
            inner join bbdw.FACT_RECURRINGGIFTINSTALLMENTPAYMENT RGPAYMENT on RGINSTALLMENT.RECURRINGGIFTINSTALLMENTFACTID = RGPAYMENT.RECURRINGGIFTINSTALLMENTFACTID
            inner join bbdw.FACT_REVENUE FR3 on FR3.REVENUEFACTID = RGPAYMENT.REVENUEFACTID and FR3.[REVENUEDATEDIMID] >= @STARTDATE2DIMID and FR3.[REVENUEDATEDIMID] <= @ENDDATE2DIMID
            where DRC.REVENUETRANSACTIONTYPECODE = 2
                and DMP.MEMBERSHIPPROGRAMSYSTEMID = @CONTEXTID
            group by DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL

            union all

            --Membership payments - Recurring gift addons

            select coalesce(SUM(FR2.REVENUEAPPLICATIONAMOUNT), 0) as AMOUNT, DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL
            from BBDW.FACT_MEMBERSHIPADDON MA
            inner join bbdw.FACT_MEMBERSHIPTRANSACTION FMT on MA.MEMBERSHIPTRANSACTIONFACTID = FMT.MEMBERSHIPTRANSACTIONFACTID
            inner join bbdw.DIM_MEMBERSHIPPROGRAM DMP on FMT.MEMBERSHIPPROGRAMDIMID = DMP.MEMBERSHIPPROGRAMDIMID
            inner join bbdw.FACT_REVENUE FR on FMT.REVENUEFACTID = FR.REVENUEFACTID
            inner join bbdw.DIM_REVENUECODE DRC on FR.REVENUECODEDIMID = DRC.REVENUECODEDIMID
            inner join bbdw.FACT_REVENUE FR2 on MA.REVENUEFACTID = FR2.REVENUEFACTID and FR2.[REVENUEDATEDIMID] >= @STARTDATE2DIMID and FR2.[REVENUEDATEDIMID] <= @ENDDATE2DIMID
            where DRC.REVENUETRANSACTIONTYPECODE = 2
                and DMP.MEMBERSHIPPROGRAMSYSTEMID = @CONTEXTID
            group by DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL

            union all

            --Membership payments - Installment plans

            select coalesce(SUM(IPAYMENT.INSTALLMENTPAYMENTAMOUNT), 0) as AMOUNT, DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL
            from bbdw.DIM_MEMBERSHIPPROGRAM DMP
            inner join bbdw.FACT_MEMBERSHIPTRANSACTION FMT on FMT.MEMBERSHIPPROGRAMDIMID = DMP.MEMBERSHIPPROGRAMDIMID
            inner join bbdw.FACT_REVENUE FR on FMT.REVENUEFACTID = FR.REVENUEFACTID
            inner join bbdw.DIM_REVENUECODE DRC on FR.REVENUECODEDIMID = DRC.REVENUECODEDIMID
            inner join bbdw.FACT_REVENUE FR2 on Fr2.REVENUESEQUENCEID = FR.REVENUESEQUENCEID
            inner join bbdw.FACT_INSTALLMENT INSTALLMENT on installment.REVENUEFACTID = fr2.REVENUEFACTID
            inner join bbdw.FACT_INSTALLMENTPAYMENT IPAYMENT on INSTALLMENT.INSTALLMENTFACTID = IPAYMENT.INSTALLMENTFACTID
            inner join bbdw.FACT_REVENUE FR3 on IPAYMENT.REVENUEFACTID = FR3.REVENUEFACTID and FR3.[REVENUEDATEDIMID] >= @STARTDATE2DIMID and FR3.[REVENUEDATEDIMID] <= @ENDDATE2DIMID
            where DRC.REVENUETRANSACTIONTYPECODE = 15
                and DMP.MEMBERSHIPPROGRAMSYSTEMID = @CONTEXTID
            group by DMP.MEMBERSHIPLEVELSYSTEMID, DMP.MEMBERSHIPLEVEL) as REV2
        group by REV2.MEMBERSHIPLEVELSYSTEMID, REV2.MEMBERSHIPLEVEL)

select top 5
  coalesce(d1.[MEMBERSHIPLEVEL], d2.[MEMBERSHIPLEVEL]) as [MEMBERSHIPLEVEL], 
  coalesce(d1.[TOTALREVENUE], 0) as [DATERANGE1REVENUE], 
  coalesce(d2.[TOTALREVENUE], 0) as [DATERANGE2REVENUE]
from [DATERANGE1] d1
full join [DATERANGE2] d2 on d1.[MEMBERSHIPLEVELSYSTEMID] = d2.[MEMBERSHIPLEVELSYSTEMID]
order by d2.[TOTALREVENUE] desc