USP_DATALIST_MEMBERSHIPACTION

create procedure BBDW.USP_DATALIST_MEMBERSHIPACTION(
  @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 
      case when ma.[MEMBERSHIPACTION] = 'Rejoin' then 'Join' else ma.[MEMBERSHIPACTION] end as [MEMBERSHIPACTION],
      isnull(count(distinct mt.[MEMBERSHIPDIMID]), 0) as [MEMBERSHIPS]
    from BBDW.[v_DIM_MEMBERSHIPACTION] ma
    inner join BBDW.[v_FACT_MEMBERSHIPTRANSACTION] mt on ma.[MEMBERSHIPACTIONDIMID] = mt.[MEMBERSHIPACTIONDIMID] and mt.[MEMBERSHIPTRANSACTIONDATEDIMID] >= @STARTDATE1DIMID and mt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= @ENDDATE1DIMID
    inner join BBDW.[v_DIM_MEMBERSHIPPROGRAM] mp on mt.[MEMBERSHIPPROGRAMDIMID] = mp.[MEMBERSHIPPROGRAMDIMID] and mp.[MEMBERSHIPPROGRAMSYSTEMID] = @CONTEXTID
      where 
      ma.MEMBERSHIPACTION in ('Join', 'Renew', 'Rejoin')
      group by case when ma.[MEMBERSHIPACTION] = 'Rejoin' then 'Join' else ma.[MEMBERSHIPACTION] end),

 [DATERANGE2]  as
    (select 
    case when ma.[MEMBERSHIPACTION] = 'Rejoin' then 'Join' else ma.[MEMBERSHIPACTION] end as [MEMBERSHIPACTION],
    isnull(count(distinct mt.[MEMBERSHIPDIMID]), 0) as [MEMBERSHIPS]
    from BBDW.[v_DIM_MEMBERSHIPACTION] ma
    inner join BBDW.[v_FACT_MEMBERSHIPTRANSACTION] mt on ma.[MEMBERSHIPACTIONDIMID] = mt.[MEMBERSHIPACTIONDIMID] and mt.[MEMBERSHIPTRANSACTIONDATEDIMID] >= @STARTDATE2DIMID and mt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= @ENDDATE2DIMID
    inner join BBDW.[v_DIM_MEMBERSHIPPROGRAM] mp on mt.[MEMBERSHIPPROGRAMDIMID] = mp.[MEMBERSHIPPROGRAMDIMID] and mp.[MEMBERSHIPPROGRAMSYSTEMID] = @CONTEXTID
    where 
    ma.MEMBERSHIPACTION in ('Join', 'Renew', 'Rejoin')
    group by case when ma.[MEMBERSHIPACTION] = 'Rejoin' then 'Join' else ma.[MEMBERSHIPACTION] end)

select 
  isnull(d1.[MEMBERSHIPACTION], d2.[MEMBERSHIPACTION]) as [MEMBERSHIPACTION], 
  coalesce(d1.[MEMBERSHIPS], 0) as [DATERANGE1MEMBERSHIPS], 
  coalesce(d2.[MEMBERSHIPS], 0) as [DATERANGE2MEMBERSHIPS]
from [DATERANGE1] d1
full join [DATERANGE2] d2 on d1.[MEMBERSHIPACTION] = d2.[MEMBERSHIPACTION]