USP_REPORT_MEMBERSHIPACTIVITY

        create procedure BBDW.[USP_REPORT_MEMBERSHIPACTIVITY] (
          @GROUP1 nvarchar(30),
          @GROUP2 nvarchar(30),
          @GROUP3 nvarchar(30),
          @DATA1 nvarchar(30),
          @DATA2 nvarchar(30),
          @DATA3 nvarchar(30),
          @DATA4 nvarchar(30),
          @DATA5 nvarchar(30),
          @DATA6 nvarchar(30),
          @DATA7 nvarchar(30),
          @DATA8 nvarchar(30),
          @DATA9 nvarchar(30),
          @DATA10 nvarchar(30),
          @DATA11 nvarchar(30),
          @DATA12 nvarchar(30),          
          @STARTDATE datetime,
          @ENDDATE datetime,
          @MEMBERSHIPPROGRAMS nvarchar(max),
          @PROGRAMFILTERTYPE nvarchar(100) = '',
          @PROGRAMFILTERTYPECODE tinyint = 0
        )

          with execute as owner

          as

            set @MEMBERSHIPPROGRAMS = REPLACE(@MEMBERSHIPPROGRAMS, '&lt;', '<');
            set @MEMBERSHIPPROGRAMS = REPLACE(@MEMBERSHIPPROGRAMS, '&gt;', '>');

            declare @STARTDATEDIMID nvarchar(30)
            declare @ENDDATEDIMID nvarchar(30)
            declare @SQL nvarchar(max)
            declare @CTESQL nvarchar(max) = ''
            declare @TableVarSQL nvarchar(max)
            declare @Select1SQL nvarchar(max) 
            declare @From1SQL nvarchar(max) 
            declare @Where1SQL nvarchar(max) 
            declare @Group1SQL nvarchar(max) 
            declare @Select2SQL nvarchar(max) 
            declare @From2SQL nvarchar(max) 
            declare @Where2SQL nvarchar(max) 
            declare @Group2SQL nvarchar(max) 
            declare @Select3SQL nvarchar(max) 
            declare @From3SQL nvarchar(max) 
            declare @Where3SQL nvarchar(max) 
            declare @Group3SQL nvarchar(max) 
            declare @Select4SQL nvarchar(max) 
            declare @From4SQL nvarchar(max) 
            declare @Where4SQL nvarchar(max) 
            declare @Group4SQL nvarchar(max) 

            if @STARTDATE is null
            set @STARTDATE = (select min([MEMBERSHIPTRANSACTIONDATE]) from BBDW.[FACT_MEMBERSHIPTRANSACTION])

            if @ENDDATE is null
            set @ENDDATE = (select max([MEMBERSHIPTRANSACTIONDATE]) from BBDW.[FACT_MEMBERSHIPTRANSACTION])

            set @STARTDATEDIMID = (year(@STARTDATE) * 10000) + (month(@STARTDATE) * 100) + day(@STARTDATE)
            set @ENDDATEDIMID = (year(@ENDDATE) * 10000) + (month(@ENDDATE) * 100) + day(@ENDDATE)

            --Add CTEs if needed

            if (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR')) and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')
              begin
                set @CTESQL = 'with [YEARS] as
        (
          select
            [CALENDARYEAR], 
            [FISCALYEAR],
            min([DATEDIMID]) [FIRSTDATEDIMID],
            max([DATEDIMID]) [LASTDATEDIMID]
          from BBDW.[v_DIM_DATE] 
          where [DATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID +
          ' group by [CALENDARYEAR], [FISCALYEAR]
        ),

        [MONTHS] as
        (
          select
            [CALENDARYEAR],
            [FISCALYEAR],
            [CALENDARMONTH],
            [CALENDARMONTHYEARNAME], 
            min([DATEDIMID]) [FIRSTDATEDIMID],
            max([DATEDIMID]) [LASTDATEDIMID]
          from BBDW.[v_DIM_DATE] 
          where [DATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID + 
          ' group by [CALENDARMONTHYEARNAME], [CALENDARYEAR], [CALENDARMONTH], [FISCALYEAR]
        ) '
              end

            if (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR')) and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')
              begin
                set @CTESQL = 'with [YEARS] as
        (
          select
            [CALENDARYEAR], 
            [FISCALYEAR],
            min([DATEDIMID]) [FIRSTDATEDIMID],
            max([DATEDIMID]) [LASTDATEDIMID]
          from BBDW.[v_DIM_DATE] 
          where [DATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID +
          ' group by [CALENDARYEAR], [FISCALYEAR]
        ) '
              end

            if (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME') and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR'))
              begin
                set @CTESQL = 'with [MONTHS] as
        (
          select
            [CALENDARYEAR],
            [FISCALYEAR],
            [CALENDARMONTH],
            [CALENDARMONTHYEARNAME], 
            min([DATEDIMID]) [FIRSTDATEDIMID],
            max([DATEDIMID]) [LASTDATEDIMID]
          from BBDW.[v_DIM_DATE] 
          where [DATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID +
          ' group by [CALENDARMONTHYEARNAME], [CALENDARYEAR], [CALENDARMONTH], [FISCALYEAR]
        ) '
              end

            --Build core SQL

            set @Select1SQL = 'select @PROGRAMFILTERTYPE as [PROGRAMFILTERTYPE], '
            set @From1SQL = 'from ('
            set @Where1SQL = ') as m '
            set @Group1SQL = ''

            if @Group1 is not null
              begin
            set @Group1SQL = ' group by '    
              end

          set @TableVarSQL = 'declare @MEMBERSHIPPROGRAMIDTABLE table (MEMBERSHIPPROGRAMSYSTEMID uniqueidentifier); '
          -- 0 = All, 1 = Selected, 2 = None

          if @PROGRAMFILTERTYPECODE = 0
          begin
              set @TableVarSQL = @TableVarSQL + 'insert into @MEMBERSHIPPROGRAMIDTABLE (MEMBERSHIPPROGRAMSYSTEMID)
                select distinct [MEMBERSHIPPROGRAMSYSTEMID] 
                from BBDW.[DIM_MEMBERSHIPPROGRAM] 
                where MEMBERSHIPPROGRAMSYSTEMID <> ''00000000-0000-0000-0000-000000000000''
                    and MEMBERSHIPPROGRAMISACTIVE = 1; '
          end
          else if @PROGRAMFILTERTYPECODE = 1
          begin
              set @TableVarSQL = @TableVarSQL + 'insert into @MEMBERSHIPPROGRAMIDTABLE (MEMBERSHIPPROGRAMSYSTEMID)
                select [MEMBERSHIPPROGRAMID] from BBDW.[UFN_MEMBERSHIPPROGRAMS_FROMITEMLISTXML] (''' + @MEMBERSHIPPROGRAMS + '''); '
          end

            --Handle Year/Month CTE joins

            if (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))
            begin
              set @Select2SQL = 'select dm.[MEMBERSHIPDIMID], '
              set @Group2SQL = ' group by dm.[MEMBERSHIPDIMID], '
              set @From2SQL = 'from BBDW.[v_FACT_MEMBERSHIPTRANSACTION] fmt
        left join BBDW.[v_DIM_MEMBERSHIP] dm on dm.[MEMBERSHIPDIMID] = fmt.[MEMBERSHIPDIMID]
        left join BBDW.[v_DIM_MEMBERSHIPPROGRAM] dmp on dmp.[MEMBERSHIPPROGRAMDIMID] = fmt.[MEMBERSHIPPROGRAMDIMID] 
        inner join @MEMBERSHIPPROGRAMIDTABLE tmp on tmp.MEMBERSHIPPROGRAMSYSTEMID = dmp.[MEMBERSHIPPROGRAMSYSTEMID]
        left join BBDW.[v_DIM_MEMBERSHIPSTATUS] ms on ms.[MEMBERSHIPSTATUSDIMID] = fmt.[MEMBERSHIPSTATUSDIMID]
        left join BBDW.[v_DIM_MEMBERSHIPACTION] dma on dma.[MEMBERSHIPACTIONDIMID] = fmt.[MEMBERSHIPACTIONDIMID] '
              set @Where2SQL = 'where (fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= ' + @ENDDATEDIMID + ' and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] >= ' + @STARTDATEDIMID + ') '
            end

        if (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))
            begin
              set @Select2SQL = 'select dm.[MEMBERSHIPDIMID], '
              set @Group2SQL = ' group by dm.[MEMBERSHIPDIMID], '
              set @From2SQL = 'from BBDW.[v_FACT_MEMBERSHIPTRANSACTION] fmt
        left join BBDW.[v_DIM_MEMBERSHIP] dm on dm.[MEMBERSHIPDIMID] = fmt.[MEMBERSHIPDIMID]
        left join BBDW.[v_DIM_MEMBERSHIPPROGRAM] dmp on dmp.[MEMBERSHIPPROGRAMDIMID] = fmt.[MEMBERSHIPPROGRAMDIMID] 
        inner join @MEMBERSHIPPROGRAMIDTABLE tmp on tmp.MEMBERSHIPPROGRAMSYSTEMID = dmp.[MEMBERSHIPPROGRAMSYSTEMID]
        left join BBDW.[v_DIM_MEMBERSHIPSTATUS] ms on ms.[MEMBERSHIPSTATUSDIMID] = fmt.[MEMBERSHIPSTATUSDIMID]
        left join BBDW.[v_DIM_MEMBERSHIPACTION] dma on dma.[MEMBERSHIPACTIONDIMID] = fmt.[MEMBERSHIPACTIONDIMID] '
              set @Where2SQL = 'where (fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= ' + @ENDDATEDIMID + ' and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] >= ' + @STARTDATEDIMID + ')'
            end



        if (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
            and (@GROUP1 <> 'CALENDARMONTHYEARNAME' or @GROUP2 <> 'CALENDARMONTHYEARNAME' or @GROUP3 <> 'CALENDARMONTHYEARNAME')
            begin
              set @Select3SQL =  ' union all '
            end

            if @GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR')
              begin

                set @Select3SQL = @Select3SQL + 'select dm.[MEMBERSHIPDIMID], '
                set @From3SQL = 'from BBDW.[v_FACT_MEMBERSHIPTRANSACTION] fmt
        left join BBDW.[v_DIM_MEMBERSHIP] dm on dm.[MEMBERSHIPDIMID] = fmt.[MEMBERSHIPDIMID] 
        left join BBDW.[v_DIM_MEMBERSHIPPROGRAM] dmp on dmp.[MEMBERSHIPPROGRAMDIMID] = fmt.[MEMBERSHIPPROGRAMDIMID] 
        inner join @MEMBERSHIPPROGRAMIDTABLE tmp on tmp.MEMBERSHIPPROGRAMSYSTEMID = dmp.[MEMBERSHIPPROGRAMSYSTEMID]
        left join BBDW.[v_DIM_MEMBERSHIPSTATUS] ms on ms.[MEMBERSHIPSTATUSDIMID] = fmt.[MEMBERSHIPSTATUSDIMID]
        left join BBDW.[v_DIM_MEMBERSHIPACTION] dma on dma.[MEMBERSHIPACTIONDIMID] = fmt.[MEMBERSHIPACTIONDIMID]
        left join [YEARS] cy on 1=1 '
                set @Where3SQL = 'where (fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= ' + @ENDDATEDIMID + ' and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] >= ' + @STARTDATEDIMID + ')'
                set @Group3SQL = ' group by dm.[MEMBERSHIPDIMID], '
              end

            if @GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME'
              begin
                set @Select4SQL = ' union all select dm.[MEMBERSHIPDIMID], '
                set @From4SQL = 'from BBDW.[v_FACT_MEMBERSHIPTRANSACTION] fmt
        left join BBDW.[v_DIM_MEMBERSHIP] dm on dm.[MEMBERSHIPDIMID] = fmt.[MEMBERSHIPDIMID] 
        left join BBDW.[v_DIM_MEMBERSHIPPROGRAM] dmp on dmp.[MEMBERSHIPPROGRAMDIMID] = fmt.[MEMBERSHIPPROGRAMDIMID] 
        inner join @MEMBERSHIPPROGRAMIDTABLE tmp on tmp.MEMBERSHIPPROGRAMSYSTEMID = dmp.[MEMBERSHIPPROGRAMSYSTEMID]
        left join BBDW.[v_DIM_MEMBERSHIPSTATUS] ms on ms.[MEMBERSHIPSTATUSDIMID] = fmt.[MEMBERSHIPSTATUSDIMID]
        left join BBDW.[v_DIM_MEMBERSHIPACTION] dma on dma.[MEMBERSHIPACTIONDIMID] = fmt.[MEMBERSHIPACTIONDIMID]
        left join [MONTHS] cm on 1=1 '
                set @Where4SQL = 'where (fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= ' + @ENDDATEDIMID + ' and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] >= ' + @STARTDATEDIMID + ')'
                set @Group4SQL = ' group by dm.[MEMBERSHIPDIMID], '
              end

            --Add optional joins

            if @GROUP1 in ('COUNTRY', 'STATE', 'COUNTY', 'CITY', 'POSTCODE', 'CLASSOF', 'EDUCATIONDEGREE', 'ETHNICITY') or @GROUP2 in ('COUNTRY', 'STATE', 'COUNTY', 'CITY', 'POSTCODE', 'CLASSOF', 'EDUCATIONDEGREE', 'ETHNICITY') or @GROUP3 in ('COUNTRY', 'STATE', 'COUNTY', 'CITY', 'POSTCODE', 'CLASSOF', 'EDUCATIONDEGREE', 'ETHNICITY')

              begin
                set @From2SQL = @From2SQL + 'left join BBDW.[v_FACT_MEMBER] fmp on fmp.[MEMBERSHIPDIMID] = dm.[MEMBERSHIPDIMID] 
        inner join BBDW.[v_DIM_MEMBERFLAG] dmf on fmp.[MEMBERFLAGDIMID] = dmf.[MEMBERFLAGDIMID] and dmf.[MEMBERISPRIMARY] = 1
        left join BBDW.[v_DIM_CONSTITUENTLOOKUP] dcl on dcl.[CONSTITUENTDIMID] = fmp.[CONSTITUENTDIMID] '
                set @From3SQL = @From3SQL + 'left join BBDW.[v_FACT_MEMBER] fmp on fmp.[MEMBERSHIPDIMID] = dm.[MEMBERSHIPDIMID] 
        inner join BBDW.[v_DIM_MEMBERFLAG] dmf on fmp.[MEMBERFLAGDIMID] = dmf.[MEMBERFLAGDIMID] and dmf.[MEMBERISPRIMARY] = 1
        left join BBDW.[v_DIM_CONSTITUENTLOOKUP] dcl on dcl.[CONSTITUENTDIMID] = fmp.[CONSTITUENTDIMID] '
                set @From4SQL = @From4SQL + 'left join BBDW.[v_FACT_MEMBER] fmp on fmp.[MEMBERSHIPDIMID] = dm.[MEMBERSHIPDIMID] 
        inner join BBDW.[v_DIM_MEMBERFLAG] dmf on fmp.[MEMBERFLAGDIMID] = dmf.[MEMBERFLAGDIMID] and dmf.[MEMBERISPRIMARY] = 1
        left join BBDW.[v_DIM_CONSTITUENTLOOKUP] dcl on dcl.[CONSTITUENTDIMID] = fmp.[CONSTITUENTDIMID] '
              end

            if @GROUP1 in ('CLASSOF', 'EDUCATIONDEGREE') or @GROUP2 in ('CLASSOF', 'EDUCATIONDEGREE') or @GROUP3 in ('CLASSOF', 'EDUCATIONDEGREE')

              begin
                set @From2SQL = @From2SQL + 'left join BBDW.[v_DIM_EDUCATION] de on de.[CONSTITUENTDIMID] = fmp.[CONSTITUENTDIMID] and de.[ISPRIMARYRECORD] = 1 and de.[EDUCATIONINSTITUTIONISAFFLIATED] = 1 '
                set @From3SQL = @From3SQL + 'left join BBDW.[v_DIM_EDUCATION] de on de.[CONSTITUENTDIMID] = fmp.[CONSTITUENTDIMID] and de.[ISPRIMARYRECORD] = 1 and de.[EDUCATIONINSTITUTIONISAFFLIATED] = 1 '
                set @From4SQL = @From4SQL + 'left join BBDW.[v_DIM_EDUCATION] de on de.[CONSTITUENTDIMID] = fmp.[CONSTITUENTDIMID] and de.[ISPRIMARYRECORD] = 1 and de.[EDUCATIONINSTITUTIONISAFFLIATED] = 1 '
              end

            if @GROUP1 = ('ETHNICITY') or @GROUP2 = ('ETHNICITY') or @GROUP3 = ('ETHNICITY')

              begin
                set @From2SQL = @From2SQL + 'left join BBDW.[v_FACT_CONSTITUENTETHNICITY] fe on fe.[CONSTITUENTDIMID] = fmp.CONSTITUENTDIMID
        left join BBDW.[v_DIM_ETHNICITY] e on fe.[ETHNICITYDIMID] = e.[ETHNICITYDIMID] '
                set @From3SQL = @From3SQL + 'left join BBDW.[v_FACT_CONSTITUENTETHNICITY] fe on fe.[CONSTITUENTDIMID] = fmp.CONSTITUENTDIMID
        left join BBDW.[v_DIM_ETHNICITY] e on fe.[ETHNICITYDIMID] = e.[ETHNICITYDIMID] '
                set @From4SQL = @From4SQL + 'left join BBDW.[v_FACT_CONSTITUENTETHNICITY] fe on fe.[CONSTITUENTDIMID] = fmp.CONSTITUENTDIMID
        left join BBDW.[v_DIM_ETHNICITY] e on fe.[ETHNICITYDIMID] = e.[ETHNICITYDIMID] '
              end

            --Add selected groupings

            if @GROUP1 = 'MEMBERSHIPPROGRAM' or @GROUP2 = 'MEMBERSHIPPROGRAM' or @GROUP3 = 'MEMBERSHIPPROGRAM'
              begin
                set @Select1SQL = @Select1SQL + 'm.[MEMBERSHIPPROGRAM], ' 
                set @Select2SQL = @Select2SQL + 'dmp.[MEMBERSHIPPROGRAM], '
                set @Select3SQL = @Select3SQL + 'dmp.[MEMBERSHIPPROGRAM], '
                set @Select4SQL = @Select4SQL + 'dmp.[MEMBERSHIPPROGRAM], '
                set @Group1SQL = @Group1SQL + 'm.[MEMBERSHIPPROGRAM], '
                set @Group2SQL = @Group2SQL + 'dmp.[MEMBERSHIPPROGRAM], '
                set @Group3SQL = @Group3SQL + 'dmp.[MEMBERSHIPPROGRAM], '
                set @Group4SQL = @Group4SQL + 'dmp.[MEMBERSHIPPROGRAM], '
              end

            if @GROUP1 = 'MEMBERSHIPLEVEL' or @GROUP2 = 'MEMBERSHIPLEVEL' or @GROUP3 = 'MEMBERSHIPLEVEL'
              begin
                set @Select1SQL = @Select1SQL + 'm.[MEMBERSHIPLEVEL], ' 
                set @Select2SQL = @Select2SQL + 'dmp.[MEMBERSHIPLEVEL], '
                set @Select3SQL = @Select3SQL + 'dmp.[MEMBERSHIPLEVEL], '
                set @Select4SQL = @Select4SQL + 'dmp.[MEMBERSHIPLEVEL], '
                set @Group1SQL = @Group1SQL + 'm.[MEMBERSHIPLEVEL], '
                set @Group2SQL = @Group2SQL + 'dmp.[MEMBERSHIPLEVEL], '
                set @Group3SQL = @Group3SQL + 'dmp.[MEMBERSHIPLEVEL], '
                set @Group4SQL = @Group4SQL + 'dmp.[MEMBERSHIPLEVEL], '
              end

            if @GROUP1 = 'MEMBERSHIPREPORTINGGROUP' or @GROUP2 = 'MEMBERSHIPREPORTINGGROUP' or @GROUP3 = 'MEMBERSHIPREPORTINGGROUP'
              begin
                set @Select1SQL = @Select1SQL + 'm.[MEMBERSHIPREPORTINGGROUP], ' 
                set @Select2SQL = @Select2SQL + 'dmp.[MEMBERSHIPREPORTINGGROUP], '
                set @Select3SQL = @Select3SQL + 'dmp.[MEMBERSHIPREPORTINGGROUP], '
                set @Select4SQL = @Select4SQL + 'dmp.[MEMBERSHIPREPORTINGGROUP], '
                set @Group1SQL = @Group1SQL + 'm.[MEMBERSHIPREPORTINGGROUP], '
                set @Group2SQL = @Group2SQL + 'dmp.[MEMBERSHIPREPORTINGGROUP], '
                set @Group3SQL = @Group3SQL + 'dmp.[MEMBERSHIPREPORTINGGROUP], '
                set @Group4SQL = @Group4SQL + 'dmp.[MEMBERSHIPREPORTINGGROUP], '
              end

            if @GROUP1 = 'CLASSOF' or @GROUP2 = 'CLASSOF' or @GROUP3 = 'CLASSOF'
              begin
                set @Select1SQL = @Select1SQL + 'm.[CLASSOF], ' 
                set @Select2SQL = @Select2SQL + 'isnull(de.[CLASSOF], 0) as [CLASSOF], '
                set @Select3SQL = @Select3SQL + 'isnull(de.[CLASSOF], 0) as [CLASSOF], '
                set @Select4SQL = @Select4SQL + 'isnull(de.[CLASSOF], 0) as [CLASSOF], '
                set @Group1SQL = @Group1SQL + 'm.[CLASSOF], '
                set @Group2SQL = @Group2SQL + 'isnull(de.[CLASSOF], 0), '
                set @Group3SQL = @Group3SQL + 'isnull(de.[CLASSOF], 0), '
                set @Group4SQL = @Group4SQL + 'isnull(de.[CLASSOF], 0), '
              end

            if @GROUP1 = 'EDUCATIONDEGREE' or @GROUP2 = 'EDUCATIONDEGREE' or @GROUP3 = 'EDUCATIONDEGREE'
              begin
                set @Select1SQL = @Select1SQL + 'm.[EDUCATIONDEGREE], ' 
                set @Select2SQL = @Select2SQL + 'case when de.[ACADEMICCATALOGDEGREE] is null and de.[EDUCATIONDEGREE] is null then ''No Educational Degree''
when de.[ACADEMICCATALOGDEGREE] <> ''No Academic Catalog Degree'' then de.[ACADEMICCATALOGDEGREE]
else de.[EDUCATIONDEGREE]
end as [EDUCATIONDEGREE], '
                set @Select3SQL = @Select3SQL + 'case when de.[ACADEMICCATALOGDEGREE] is null and de.[EDUCATIONDEGREE] is null then ''No Educational Degree''
when de.[ACADEMICCATALOGDEGREE] <> ''No Academic Catalog Degree'' then de.[ACADEMICCATALOGDEGREE]
else de.[EDUCATIONDEGREE]
end as [EDUCATIONDEGREE], '
                set @Select4SQL = @Select4SQL + 'case when de.[ACADEMICCATALOGDEGREE] is null and de.[EDUCATIONDEGREE] is null then ''No Educational Degree''
when de.[ACADEMICCATALOGDEGREE] <> ''No Academic Catalog Degree'' then de.[ACADEMICCATALOGDEGREE]
else de.[EDUCATIONDEGREE]
end as [EDUCATIONDEGREE], '
                set @Group1SQL = @Group1SQL + 'm.[EDUCATIONDEGREE], '
                set @Group2SQL = @Group2SQL + 'case when de.[ACADEMICCATALOGDEGREE] is null and de.[EDUCATIONDEGREE] is null then ''No Educational Degree''
when de.[ACADEMICCATALOGDEGREE] <> ''No Academic Catalog Degree'' then de.[ACADEMICCATALOGDEGREE]
else de.[EDUCATIONDEGREE]
end, '
                set @Group3SQL = @Group3SQL + 'case when de.[ACADEMICCATALOGDEGREE] is null and de.[EDUCATIONDEGREE] is null then ''No Educational Degree''
when de.[ACADEMICCATALOGDEGREE] <> ''No Academic Catalog Degree'' then de.[ACADEMICCATALOGDEGREE]
else de.[EDUCATIONDEGREE]
end, '
                set @Group4SQL = @Group4SQL + 'case when de.[ACADEMICCATALOGDEGREE] is null and de.[EDUCATIONDEGREE] is null then ''No Educational Degree''
when de.[ACADEMICCATALOGDEGREE] <> ''No Academic Catalog Degree'' then de.[ACADEMICCATALOGDEGREE]
else de.[EDUCATIONDEGREE]
end, '
              end

            if @GROUP1 = 'COUNTRY' or @GROUP2 = 'COUNTRY' or @GROUP3 = 'COUNTRY'
              begin
                set @Select1SQL = @Select1SQL + 'm.[COUNTRY], ' 
                set @Select2SQL = @Select2SQL + 'dcl.[PRIMARYADDRESSCOUNTRY] as [COUNTRY], '
                set @Select3SQL = @Select3SQL + 'dcl.[PRIMARYADDRESSCOUNTRY] as [COUNTRY], '
                set @Select4SQL = @Select4SQL + 'dcl.[PRIMARYADDRESSCOUNTRY] as [COUNTRY], '
                set @Group1SQL = @Group1SQL + 'm.[COUNTRY], '        
                set @Group2SQL = @Group2SQL + 'dcl.[PRIMARYADDRESSCOUNTRY], '
                set @Group3SQL = @Group3SQL + 'dcl.[PRIMARYADDRESSCOUNTRY], '
                set @Group4SQL = @Group4SQL + 'dcl.[PRIMARYADDRESSCOUNTRY], '
              end

            if @GROUP1 = 'STATE' or @GROUP2 = 'STATE' or @GROUP3 = 'STATE'
              begin
                set @Select1SQL = @Select1SQL + 'm.[STATE], ' 
                set @Select2SQL = @Select2SQL + 'dcl.[PRIMARYADDRESSSTATE] as [STATE], '
                set @Select3SQL = @Select3SQL + 'dcl.[PRIMARYADDRESSSTATE] as [STATE], '
                set @Select4SQL = @Select4SQL + 'dcl.[PRIMARYADDRESSSTATE] as [STATE], '
                set @Group1SQL = @Group1SQL + 'm.[STATE], '
                set @Group2SQL = @Group2SQL + 'dcl.[PRIMARYADDRESSSTATE], '
                set @Group3SQL = @Group3SQL + 'dcl.[PRIMARYADDRESSSTATE], '
                set @Group4SQL = @Group4SQL + 'dcl.[PRIMARYADDRESSSTATE], '
              end

            if @GROUP1 = 'COUNTY' or @GROUP2 = 'COUNTY' or @GROUP3 = 'COUNTY'
              begin
                set @Select1SQL = @Select1SQL + 'm.[COUNTY], ' 
                set @Select2SQL = @Select2SQL + 'dcl.[PRIMARYADDRESSCOUNTY] as [COUNTY], '
                set @Select3SQL = @Select3SQL + 'dcl.[PRIMARYADDRESSCOUNTY] as [COUNTY], '
                set @Select4SQL = @Select4SQL + 'dcl.[PRIMARYADDRESSCOUNTY] as [COUNTY], '
                set @Group1SQL = @Group1SQL + 'm.[COUNTY], '
                set @Group2SQL = @Group2SQL + 'dcl.[PRIMARYADDRESSCOUNTY], '
                set @Group3SQL = @Group3SQL + 'dcl.[PRIMARYADDRESSCOUNTY], '
                set @Group4SQL = @Group4SQL + 'dcl.[PRIMARYADDRESSCOUNTY], '
              end

            if @GROUP1 = 'CITY' or @GROUP2 = 'CITY' or @GROUP3 = 'CITY'
              begin
                set @Select1SQL = @Select1SQL + 'm.[CITY], ' 
                set @Select2SQL = @Select2SQL + 'dcl.[PRIMARYADDRESSCITY] as [CITY], '
                set @Select3SQL = @Select3SQL + 'dcl.[PRIMARYADDRESSCITY] as [CITY], '
                set @Select4SQL = @Select4SQL + 'dcl.[PRIMARYADDRESSCITY] as [CITY], '
                set @Group1SQL = @Group1SQL + 'm.[CITY], '
                set @Group2SQL = @Group2SQL + 'dcl.[PRIMARYADDRESSCITY], '
                set @Group3SQL = @Group3SQL + 'dcl.[PRIMARYADDRESSCITY], '
                set @Group4SQL = @Group4SQL + 'dcl.[PRIMARYADDRESSCITY], '
              end

            if @GROUP1 = 'POSTCODE' or @GROUP2 = 'POSTCODE' or @GROUP3 = 'POSTCODE'
              begin
                set @Select1SQL = @Select1SQL + 'm.[POSTCODE], ' 
                set @Select2SQL = @Select2SQL + 'dcl.[PRIMARYADDRESSPOSTCODE] as [POSTCODE], '
                set @Select3SQL = @Select3SQL + 'dcl.[PRIMARYADDRESSPOSTCODE] as [POSTCODE], '
                set @Select4SQL = @Select4SQL + 'dcl.[PRIMARYADDRESSPOSTCODE] as [POSTCODE], '
                set @Group1SQL = @Group1SQL + 'm.[POSTCODE], '
                set @Group2SQL = @Group2SQL + 'dcl.[PRIMARYADDRESSPOSTCODE], '
                set @Group3SQL = @Group3SQL + 'dcl.[PRIMARYADDRESSPOSTCODE], '
                set @Group4SQL = @Group4SQL + 'dcl.[PRIMARYADDRESSPOSTCODE], '
              end

            if @GROUP1 = 'ETHNICITY' or @GROUP2 = 'ETHNICITY' or @GROUP3 = 'ETHNICITY'
              begin
                set @Select1SQL = @Select1SQL + 'm.[ETHNICITY], ' 
                set @Select2SQL = @Select2SQL + 'isnull(e.[ETHNICITY], ''No Ethnicity'') as [ETHNICITY], '
                set @Select3SQL = @Select3SQL + 'isnull(e.[ETHNICITY], ''No Ethnicity'') as [ETHNICITY], '
                set @Select4SQL = @Select4SQL + 'isnull(e.[ETHNICITY], ''No Ethnicity'') as [ETHNICITY], '
                set @Group1SQL = @Group1SQL + 'm.[ETHNICITY], '
                set @Group2SQL = @Group2SQL + 'isnull(e.[ETHNICITY], ''No Ethnicity''), '
                set @Group3SQL = @Group3SQL + 'isnull(e.[ETHNICITY], ''No Ethnicity''), '
                set @Group4SQL = @Group4SQL + 'isnull(e.[ETHNICITY], ''No Ethnicity''), '
              end

            if (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR')) and
              (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')
              begin
                set @Select1SQL = @Select1SQL + 'm.[FISCALYEAR], m.[CALENDARYEAR], ' 
                set @Select2SQL = @Select2SQL + '(select top 1 [FISCALYEAR] from [YEARS] order by [LASTDATEDIMID] desc) as [FISCALYEAR], (select top 1 [CALENDARYEAR] from [YEARS] order by [LASTDATEDIMID] desc) as [CALENDARYEAR], '
                set @Select3SQL = @Select3SQL + 'cy.[FISCALYEAR], cy.[CALENDARYEAR], '
                set @Group1SQL = @Group1SQL + 'm.[FISCALYEAR], m.[CALENDARYEAR], '
                set @Group3SQL = @Group3SQL + 'cy.[FISCALYEAR], cy.[CALENDARYEAR], '
              end

            if (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME') and
              (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR'))
              begin
                set @Select1SQL = @Select1SQL + 'm.[CALENDARYEAR], m.[CALENDARMONTHYEARNAME], m.[CALENDARMONTH], m.[FISCALYEAR], ' 
                set @Select2SQL = @Select2SQL + '(select top 1 [CALENDARYEAR] from [MONTHS] order by [LASTDATEDIMID] desc) as [CALENDARYEAR], (select top 1 [CALENDARMONTHYEARNAME] from [MONTHS] order by [LASTDATEDIMID] desc) as [CALENDARMONTHYEARNAME], (select top 1 [CALENDARMONTH] from [MONTHS] order by [LASTDATEDIMID] desc) as [CALENDARMONTH], (select top 1 [FISCALYEAR] from [MONTHS] order by [LASTDATEDIMID] desc) as [FISCALYEAR], ' 
                set @Select4SQL = @Select4SQL + 'cm.[CALENDARYEAR], cm.[CALENDARMONTHYEARNAME], cm.[CALENDARMONTH], cm.[FISCALYEAR], '
                set @Group1SQL = @Group1SQL + 'm.[CALENDARYEAR], m.[CALENDARMONTHYEARNAME], m.[CALENDARMONTH], m.[FISCALYEAR], ' 
                set @Group4SQL = @Group4SQL + 'cm.[CALENDARYEAR], cm.[CALENDARMONTHYEARNAME], cm.[CALENDARMONTH], cm.[FISCALYEAR], '
              end

            if (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME') and
              (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              begin
                set @Select1SQL = @Select1SQL + 'm.[FISCALYEAR], m.[CALENDARYEAR], m.[CALENDARMONTHYEARNAME], m.[CALENDARMONTH], ' 
                set @Select2SQL = @Select2SQL + '(select top 1 [FISCALYEAR] from [MONTHS] order by [LASTDATEDIMID] desc) as [FISCALYEAR], (select top 1 [CALENDARYEAR] from [MONTHS] order by [LASTDATEDIMID] desc) as [CALENDARYEAR], (select top 1 [CALENDARMONTHYEARNAME] from [MONTHS] order by [LASTDATEDIMID] desc) as [CALENDARMONTHYEARNAME],  (select top 1 [CALENDARMONTH] from [MONTHS] order by [LASTDATEDIMID] desc) as [CALENDARMONTH], ' 
                set @Select3SQL = @Select3SQL + 'cy.[FISCALYEAR], cy.[CALENDARYEAR], (select top 1 [CALENDARMONTHYEARNAME] from [MONTHS]  where [MONTHS].[CALENDARYEAR] = cy.[CALENDARYEAR] order by [LASTDATEDIMID] desc) as [CALENDARMONTHYEARNAME], (select top 1 [CALENDARMONTH] from [MONTHS]  where [MONTHS].[CALENDARYEAR] = cy.[CALENDARYEAR] order by [LASTDATEDIMID] desc) as [CALENDARMONTH], ' 
                set @Select4SQL = @Select4SQL + 'cm.[FISCALYEAR], cm.[CALENDARYEAR], cm.[CALENDARMONTHYEARNAME], cm.[CALENDARMONTH], '
                set @Group1SQL = @Group1SQL + 'm.[FISCALYEAR], m.[CALENDARYEAR], m.[CALENDARMONTHYEARNAME], m.[CALENDARMONTH], ' 
                set @Group3SQL = @Group3SQL + 'cy.[FISCALYEAR], cy.[CALENDARYEAR], ' 
                set @Group4SQL = @Group4SQL + 'cm.[FISCALYEAR], cm.[CALENDARYEAR], cm.[CALENDARMONTHYEARNAME], cm.[CALENDARMONTH], '
              end

            --Add Totals


            --Upgrades

              --No date groupings

            if (@DATA1 = 'UPGRADE' or @DATA2 = 'UPGRADE' or @DATA3 = 'UPGRADE' or @DATA4 = 'UPGRADE' or @DATA5 = 'UPGRADE' or @DATA6 = 'UPGRADE' or @DATA7 = 'UPGRADE' or @DATA8 = 'UPGRADE' or @DATA9 = 'UPGRADE' or @DATA10 = 'UPGRADE' or @DATA11 = 'UPGRADE' or @DATA12 = 'UPGRADE')
              and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[UPGRADE]) as [UPGRADE], ' 
                set @Select2SQL = @Select2SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 2 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID + ' then 1 else 0 end) as [UPGRADE], ' 
              end

              --By Year

            if (@DATA1 = 'UPGRADE' or @DATA2 = 'UPGRADE' or @DATA3 = 'UPGRADE' or @DATA4 = 'UPGRADE' or @DATA5 = 'UPGRADE' or @DATA6 = 'UPGRADE' or @DATA7 = 'UPGRADE' or @DATA8 = 'UPGRADE' or @DATA9 = 'UPGRADE' or @DATA10 = 'UPGRADE' or @DATA11 = 'UPGRADE' or @DATA12 = 'UPGRADE')
              and (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[UPGRADE]) as [UPGRADE], ' 
                set @Select2SQL = @Select2SQL + '0 as [UPGRADE], ' 
                set @Select3SQL = @Select3SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 2 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cy.[FIRSTDATEDIMID] and cy.[LASTDATEDIMID] then 1 else 0 end) as [UPGRADE], '
              end

              --By Month

            if (@DATA1 = 'UPGRADE' or @DATA2 = 'UPGRADE' or @DATA3 = 'UPGRADE' or @DATA4 = 'UPGRADE' or @DATA5 = 'UPGRADE' or @DATA6 = 'UPGRADE' or @DATA7 = 'UPGRADE' or @DATA8 = 'UPGRADE' or @DATA9 = 'UPGRADE' or @DATA10 = 'UPGRADE' or @DATA11 = 'UPGRADE' or @DATA12 = 'UPGRADE')
              and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[UPGRADE]) as [UPGRADE], ' 
                set @Select2SQL = @Select2SQL + '0 as [UPGRADE], ' 
                set @Select3SQL = @Select3SQL + '0 as [UPGRADE], '
                set @Select4SQL = @Select4SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 2 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cm.[FIRSTDATEDIMID] and cm.[LASTDATEDIMID] then 1 else 0 end) as [UPGRADE],  '
              end

            --Downgrades

              --No date groupings

            if (@DATA1 = 'DOWNGRADE' or @DATA2 = 'DOWNGRADE' or @DATA3 = 'DOWNGRADE' or @DATA4 = 'DOWNGRADE' or @DATA5 = 'DOWNGRADE' or @DATA6 = 'DOWNGRADE' or @DATA7 = 'DOWNGRADE' or @DATA8 = 'DOWNGRADE' or @DATA9 = 'DOWNGRADE' or @DATA10 = 'DOWNGRADE' or @DATA11 = 'DOWNGRADE' or @DATA12 = 'DOWNGRADE')
              and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[DOWNGRADE]) as [DOWNGRADE], ' 
                set @Select2SQL = @Select2SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 3 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID + ' then 1 else 0 end) as [DOWNGRADE], '
              end

              --By Year

            if (@DATA1 = 'DOWNGRADE' or @DATA2 = 'DOWNGRADE' or @DATA3 = 'DOWNGRADE' or @DATA4 = 'DOWNGRADE' or @DATA5 = 'DOWNGRADE' or @DATA6 = 'DOWNGRADE' or @DATA7 = 'DOWNGRADE' or @DATA8 = 'DOWNGRADE' or @DATA9 = 'DOWNGRADE' or @DATA10 = 'DOWNGRADE' or @DATA11 = 'DOWNGRADE' or @DATA12 = 'DOWNGRADE')
              and (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[DOWNGRADE]) as [DOWNGRADE], ' 
                set @Select2SQL = @Select2SQL + '0 as [DOWNGRADE], ' 
                set @Select3SQL = @Select3SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 3 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cy.[FIRSTDATEDIMID] and cy.[LASTDATEDIMID] then 1 else 0 end) as [DOWNGRADE], '
              end

              --By Month

            if (@DATA1 = 'DOWNGRADE' or @DATA2 = 'DOWNGRADE' or @DATA3 = 'DOWNGRADE' or @DATA4 = 'DOWNGRADE' or @DATA5 = 'DOWNGRADE' or @DATA6 = 'DOWNGRADE' or @DATA7 = 'DOWNGRADE' or @DATA8 = 'DOWNGRADE' or @DATA9 = 'DOWNGRADE' or @DATA10 = 'DOWNGRADE' or @DATA11 = 'DOWNGRADE' or @DATA12 = 'DOWNGRADE')
              and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[DOWNGRADE]) as [DOWNGRADE], ' 
                set @Select2SQL = @Select2SQL + '0 as [DOWNGRADE], ' 
                set @Select3SQL = @Select3SQL + '0 as [DOWNGRADE], '
                set @Select4SQL = @Select4SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 3 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cm.[FIRSTDATEDIMID] and cm.[LASTDATEDIMID] then 1 else 0 end) as [DOWNGRADE], '
              end

            --Renew

              --No date groupings

            if (@DATA1 = 'RENEW' or @DATA2 = 'RENEW' or @DATA3 = 'RENEW' or @DATA4 = 'RENEW' or @DATA5 = 'RENEW' or @DATA6 = 'RENEW' or @DATA7 = 'RENEW' or @DATA8 = 'RENEW' or @DATA9 = 'RENEW' or @DATA10 = 'RENEW' or @DATA11 = 'RENEW' or @DATA12 = 'RENEW')
              and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[RENEW]) as [RENEW], ' 
                set @Select2SQL = @Select2SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 1 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID + ' then 1 else 0 end) as [RENEW], '
              end

              --By Year

            if (@DATA1 = 'RENEW' or @DATA2 = 'RENEW' or @DATA3 = 'RENEW' or @DATA4 = 'RENEW' or @DATA5 = 'RENEW' or @DATA6 = 'RENEW' or @DATA7 = 'RENEW' or @DATA8 = 'RENEW' or @DATA9 = 'RENEW' or @DATA10 = 'RENEW' or @DATA11 = 'RENEW' or @DATA12 = 'RENEW')
              and (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[RENEW]) as [RENEW], ' 
                set @Select2SQL = @Select2SQL + '0 as [RENEW], ' 
                set @Select3SQL = @Select3SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 1 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cy.[FIRSTDATEDIMID] and cy.[LASTDATEDIMID] then 1 else 0 end) as [RENEW], '
              end

              --By Month

            if (@DATA1 = 'RENEW' or @DATA2 = 'RENEW' or @DATA3 = 'RENEW' or @DATA4 = 'RENEW' or @DATA5 = 'RENEW' or @DATA6 = 'RENEW' or @DATA7 = 'RENEW' or @DATA8 = 'RENEW' or @DATA9 = 'RENEW' or @DATA10 = 'RENEW' or @DATA11 = 'RENEW' or @DATA12 = 'RENEW')
              and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[RENEW]) as [RENEW], ' 
                set @Select2SQL = @Select2SQL + '0 as [RENEW], ' 
                set @Select3SQL = @Select3SQL + '0 as [RENEW], '
                set @Select4SQL = @Select4SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 1 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cm.[FIRSTDATEDIMID] and cm.[LASTDATEDIMID] then 1 else 0 end) as [RENEW], '
              end

            --Join

              --No date groupings

            if (@DATA1 in ('JOIN', 'RETENTIONRATE') or @DATA2 in ('JOIN', 'RETENTIONRATE') or @DATA3 in ('JOIN', 'RETENTIONRATE') or @DATA4 in ('JOIN', 'RETENTIONRATE') or @DATA5 in ('JOIN', 'RETENTIONRATE') or @DATA6 in ('JOIN', 'RETENTIONRATE') or @DATA7 in ('JOIN', 'RETENTIONRATE') or @DATA8 in ('JOIN', 'RETENTIONRATE') or @DATA9 in ('JOIN', 'RETENTIONRATE') or @DATA10 in ('JOIN', 'RETENTIONRATE') or @DATA11 in ('JOIN', 'RETENTIONRATE') or @DATA12 in ('JOIN', 'RETENTIONRATE'))
              and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[JOIN]) as [JOIN], ' 
                set @Select2SQL = @Select2SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 0 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID + ' then 1 else 0 end) as [JOIN], '
              end

              --By Year

            if (@DATA1 in ('JOIN', 'RETENTIONRATE') or @DATA2 in ('JOIN', 'RETENTIONRATE') or @DATA3 in ('JOIN', 'RETENTIONRATE') or @DATA4 in ('JOIN', 'RETENTIONRATE') or @DATA5 in ('JOIN', 'RETENTIONRATE') or @DATA6 in ('JOIN', 'RETENTIONRATE') or @DATA7 in ('JOIN', 'RETENTIONRATE') or @DATA8 in ('JOIN', 'RETENTIONRATE') or @DATA9 in ('JOIN', 'RETENTIONRATE') or @DATA10 in ('JOIN', 'RETENTIONRATE') or @DATA11 in ('JOIN', 'RETENTIONRATE') or @DATA12 in ('JOIN', 'RETENTIONRATE'))
              and (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[JOIN]) as [JOIN], ' 
                set @Select2SQL = @Select2SQL + '0 as [JOIN], ' 
                set @Select3SQL = @Select3SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 0 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cy.[FIRSTDATEDIMID] and cy.[LASTDATEDIMID] then 1 else 0 end) as [JOIN], '
              end

              --By Month

            if (@DATA1 in ('JOIN', 'RETENTIONRATE') or @DATA2 in ('JOIN', 'RETENTIONRATE') or @DATA3 in ('JOIN', 'RETENTIONRATE') or @DATA4 in ('JOIN', 'RETENTIONRATE') or @DATA5 in ('JOIN', 'RETENTIONRATE') or @DATA6 in ('JOIN', 'RETENTIONRATE') or @DATA7 in ('JOIN', 'RETENTIONRATE') or @DATA8 in ('JOIN', 'RETENTIONRATE') or @DATA9 in ('JOIN', 'RETENTIONRATE') or @DATA10 in ('JOIN', 'RETENTIONRATE') or @DATA11 in ('JOIN', 'RETENTIONRATE') or @DATA12 in ('JOIN', 'RETENTIONRATE'))
              and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[JOIN]) as [JOIN], ' 
                set @Select2SQL = @Select2SQL + '0 as [JOIN], ' 
                set @Select3SQL = @Select3SQL + '0 as [JOIN], '
                set @Select4SQL = @Select4SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 0 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cm.[FIRSTDATEDIMID] and cm.[LASTDATEDIMID] then 1 else 0 end) as [JOIN], '
              end

            --Rejoin

              --No date groupings

            if (@DATA1 in ('REJOIN', 'RETENTIONRATE') or @DATA2 in ('REJOIN', 'RETENTIONRATE') or @DATA3 in ('REJOIN', 'RETENTIONRATE') or @DATA4 in ('REJOIN', 'RETENTIONRATE') or @DATA5 in ('REJOIN', 'RETENTIONRATE') or @DATA6 in ('REJOIN', 'RETENTIONRATE') or @DATA7 in ('REJOIN', 'RETENTIONRATE') or @DATA8 in ('REJOIN', 'RETENTIONRATE') or @DATA9 in ('REJOIN', 'RETENTIONRATE') or @DATA10 in ('REJOIN', 'RETENTIONRATE') or @DATA11 in ('REJOIN', 'RETENTIONRATE') or @DATA12 in ('REJOIN', 'RETENTIONRATE'))
              and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[REJOIN]) as [REJOIN], ' 
                set @Select2SQL = @Select2SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 5 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID + ' then 1 else 0 end) as [REJOIN], '
              end

              --By Year

            if (@DATA1 in ('REJOIN', 'RETENTIONRATE') or @DATA2 in ('REJOIN', 'RETENTIONRATE') or @DATA3 in ('REJOIN', 'RETENTIONRATE') or @DATA4 in ('REJOIN', 'RETENTIONRATE') or @DATA5 in ('REJOIN', 'RETENTIONRATE') or @DATA6 in ('REJOIN', 'RETENTIONRATE') or @DATA7 in ('REJOIN', 'RETENTIONRATE') or @DATA8 in ('REJOIN', 'RETENTIONRATE') or @DATA9 in ('REJOIN', 'RETENTIONRATE') or @DATA10 in ('REJOIN', 'RETENTIONRATE') or @DATA11 in ('REJOIN', 'RETENTIONRATE') or @DATA12 in ('REJOIN', 'RETENTIONRATE'))
              and (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[REJOIN]) as [REJOIN], ' 
                set @Select2SQL = @Select2SQL + '0 as [REJOIN], ' 
                set @Select3SQL = @Select3SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 5 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cy.[FIRSTDATEDIMID] and cy.[LASTDATEDIMID] then 1 else 0 end) as [REJOIN], '
              end

              --By Month

            if (@DATA1 in ('REJOIN', 'RETENTIONRATE') or @DATA2 in ('REJOIN', 'RETENTIONRATE') or @DATA3 in ('REJOIN', 'RETENTIONRATE') or @DATA4 in ('REJOIN', 'RETENTIONRATE') or @DATA5 in ('REJOIN', 'RETENTIONRATE') or @DATA6 in ('REJOIN', 'RETENTIONRATE') or @DATA7 in ('REJOIN', 'RETENTIONRATE') or @DATA8 in ('REJOIN', 'RETENTIONRATE') or @DATA9 in ('REJOIN', 'RETENTIONRATE') or @DATA10 in ('REJOIN', 'RETENTIONRATE') or @DATA11 in ('REJOIN', 'RETENTIONRATE') or @DATA12 in ('REJOIN', 'RETENTIONRATE'))
              and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[REJOIN]) as [REJOIN], ' 
                set @Select2SQL = @Select2SQL + '0 as [REJOIN], ' 
                set @Select3SQL = @Select3SQL + '0 as [REJOIN], '
                set @Select4SQL = @Select4SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 5 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cm.[FIRSTDATEDIMID] and cm.[LASTDATEDIMID] then 1 else 0 end) as [REJOIN], '
              end

            --Drop

              --No date groupings

            if (@DATA1 = 'DROP' or @DATA2 = 'DROP' or @DATA3 = 'DROP' or @DATA4 = 'DROP' or @DATA5 = 'DROP' or @DATA6 = 'DROP' or @DATA7 = 'DROP' or @DATA8 = 'DROP' or @DATA9 = 'DROP' or @DATA10 = 'DROP' or @DATA11 = 'DROP' or @DATA12 = 'DROP')
              and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[DROP]) as [DROP], ' 
                set @Select2SQL = @Select2SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 4 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID + ' then 1 else 0 end) as [DROP], '
              end

              --By Year

            if (@DATA1 = 'DROP' or @DATA2 = 'DROP' or @DATA3 = 'DROP' or @DATA4 = 'DROP' or @DATA5 = 'DROP' or @DATA6 = 'DROP' or @DATA7 = 'DROP' or @DATA8 = 'DROP' or @DATA9 = 'DROP' or @DATA10 = 'DROP' or @DATA11 = 'DROP' or @DATA12 = 'DROP')
              and (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[DROP]) as [DROP], ' 
                set @Select2SQL = @Select2SQL + '0 as [DROP], ' 
                set @Select3SQL = @Select3SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 4 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cy.[FIRSTDATEDIMID] and cy.[LASTDATEDIMID] then 1 else 0 end) as [DROP], '
              end

              --By Month

            if (@DATA1 = 'DROP' or @DATA2 = 'DROP' or @DATA3 = 'DROP' or @DATA4 = 'DROP' or @DATA5 = 'DROP' or @DATA6 = 'DROP' or @DATA7 = 'DROP' or @DATA8 = 'DROP' or @DATA9 = 'DROP' or @DATA10 = 'DROP' or @DATA11 = 'DROP' or @DATA12 = 'DROP')
              and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[DROP]) as [DROP], ' 
                set @Select2SQL = @Select2SQL + '0 as [DROP], ' 
                set @Select3SQL = @Select3SQL + '0 as [DROP], '
                set @Select4SQL = @Select4SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 4 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cm.[FIRSTDATEDIMID] and cm.[LASTDATEDIMID] then 1 else 0 end) as [DROP], '
              end

            --Expiring

              --No date groupings

            if (@DATA1 = 'EXPIRING' or @DATA2 = 'EXPIRING' or @DATA3 = 'EXPIRING' or @DATA4 = 'EXPIRING' or @DATA5 = 'EXPIRING' or @DATA6 = 'EXPIRING' or @DATA7 = 'EXPIRING' or @DATA8 = 'EXPIRING' or @DATA9 = 'EXPIRING' or @DATA10 = 'EXPIRING' or @DATA11 = 'EXPIRING' or @DATA12 = 'EXPIRING')
              and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[EXPIRING]) as [EXPIRING], ' 
                set @Select2SQL = @Select2SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 99 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID + ' then 1 else 0 end) as [EXPIRING], '
              end

              --By Year

            if (@DATA1 = 'EXPIRING' or @DATA2 = 'EXPIRING' or @DATA3 = 'EXPIRING' or @DATA4 = 'EXPIRING' or @DATA5 = 'EXPIRING' or @DATA6 = 'EXPIRING' or @DATA7 = 'EXPIRING' or @DATA8 = 'EXPIRING' or @DATA9 = 'EXPIRING' or @DATA10 = 'EXPIRING' or @DATA11 = 'EXPIRING' or @DATA12 = 'EXPIRING')
              and (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[EXPIRING]) as [EXPIRING], ' 
                set @Select2SQL = @Select2SQL + '0 as [EXPIRING], ' 
                set @Select3SQL = @Select3SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 99 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cy.[FIRSTDATEDIMID] and cy.[LASTDATEDIMID] then 1 else 0 end) as [EXPIRING], '
              end

              --By Month

            if (@DATA1 = 'EXPIRING' or @DATA2 = 'EXPIRING' or @DATA3 = 'EXPIRING' or @DATA4 = 'EXPIRING' or @DATA5 = 'EXPIRING' or @DATA6 = 'EXPIRING' or @DATA7 = 'EXPIRING' or @DATA8 = 'EXPIRING' or @DATA9 = 'EXPIRING' or @DATA10 = 'EXPIRING' or @DATA11 = 'EXPIRING' or @DATA12 = 'EXPIRING')
              and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[EXPIRING]) as [EXPIRING], ' 
                set @Select2SQL = @Select2SQL + '0 as [EXPIRING], ' 
                set @Select3SQL = @Select3SQL + '0 as [EXPIRING], '
                set @Select4SQL = @Select4SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 99 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cm.[FIRSTDATEDIMID] and cm.[LASTDATEDIMID] then 1 else 0 end) as [EXPIRING], '
              end      

            --Lapsed

              --No date groupings

            if (@DATA1 = 'LAPSED' or @DATA2 = 'LAPSED' or @DATA3 = 'LAPSED' or @DATA4 = 'LAPSED' or @DATA5 = 'LAPSED' or @DATA6 = 'LAPSED' or @DATA7 = 'LAPSED' or @DATA8 = 'LAPSED' or @DATA9 = 'LAPSED' or @DATA10 = 'LAPSED' or @DATA11 = 'LAPSED' or @DATA12 = 'LAPSED')
              and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[LAPSED]) as [LAPSED], ' 
                set @Select2SQL = @Select2SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 104 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID + ' then 1 else 0 end) as [LAPSED], '
              end

              --By Year

            if (@DATA1 = 'LAPSED' or @DATA2 = 'LAPSED' or @DATA3 = 'LAPSED' or @DATA4 = 'LAPSED' or @DATA5 = 'LAPSED' or @DATA6 = 'LAPSED' or @DATA7 = 'LAPSED' or @DATA8 = 'LAPSED' or @DATA9 = 'LAPSED' or @DATA10 = 'LAPSED' or @DATA11 = 'LAPSED' or @DATA12 = 'LAPSED')
              and (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[LAPSED]) as [LAPSED], ' 
                set @Select2SQL = @Select2SQL + '0 as [LAPSED], ' 
                set @Select3SQL = @Select3SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 104 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cy.[FIRSTDATEDIMID] and cy.[LASTDATEDIMID] then 1 else 0 end) as [LAPSED], '
              end

              --By Month

            if (@DATA1 = 'LAPSED' or @DATA2 = 'LAPSED' or @DATA3 = 'LAPSED' or @DATA4 = 'LAPSED' or @DATA5 = 'LAPSED' or @DATA6 = 'LAPSED' or @DATA7 = 'LAPSED' or @DATA8 = 'LAPSED' or @DATA9 = 'LAPSED' or @DATA10 = 'LAPSED' or @DATA11 = 'LAPSED' or @DATA12 = 'LAPSED')
              and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[LAPSED]) as [LAPSED], ' 
                set @Select2SQL = @Select2SQL + '0 as [LAPSED], ' 
                set @Select3SQL = @Select3SQL + '0 as [LAPSED], '
                set @Select4SQL = @Select4SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 104 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cm.[FIRSTDATEDIMID] and cm.[LASTDATEDIMID] then 1 else 0 end) as [LAPSED], '
              end      

            --Grace

              --No date groupings

            if (@DATA1 = 'LAPSED_GRACE' or @DATA2 = 'LAPSED_GRACE' or @DATA3 = 'LAPSED_GRACE' or @DATA4 = 'LAPSED_GRACE' or @DATA5 = 'LAPSED_GRACE' or @DATA6 = 'LAPSED_GRACE' or @DATA7 = 'LAPSED_GRACE' or @DATA8 = 'LAPSED_GRACE' or @DATA9 = 'LAPSED_GRACE' or @DATA10 = 'LAPSED_GRACE' or @DATA11 = 'LAPSED_GRACE' or @DATA12 = 'LAPSED_GRACE')
              and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[LAPSED_GRACE]) as [LAPSED_GRACE], ' 
                set @Select2SQL = @Select2SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 103 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID + ' then 1 else 0 end) as [LAPSED_GRACE], '
              end

              --By Year

            if (@DATA1 = 'LAPSED_GRACE' or @DATA2 = 'LAPSED_GRACE' or @DATA3 = 'LAPSED_GRACE' or @DATA4 = 'LAPSED_GRACE' or @DATA5 = 'LAPSED_GRACE' or @DATA6 = 'LAPSED_GRACE' or @DATA7 = 'LAPSED_GRACE' or @DATA8 = 'LAPSED_GRACE' or @DATA9 = 'LAPSED_GRACE' or @DATA10 = 'LAPSED_GRACE' or @DATA11 = 'LAPSED_GRACE' or @DATA12 = 'LAPSED_GRACE')
              and (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[LAPSED_GRACE]) as [LAPSED_GRACE], ' 
                set @Select2SQL = @Select2SQL + '0 as [LAPSED_GRACE], ' 
                set @Select3SQL = @Select3SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 103 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cy.[FIRSTDATEDIMID] and cy.[LASTDATEDIMID] then 1 else 0 end) as [LAPSED_GRACE], '
              end

              --By Month

            if (@DATA1 = 'LAPSED_GRACE' or @DATA2 = 'LAPSED_GRACE' or @DATA3 = 'LAPSED_GRACE' or @DATA4 = 'LAPSED_GRACE' or @DATA5 = 'LAPSED_GRACE' or @DATA6 = 'LAPSED_GRACE' or @DATA7 = 'LAPSED_GRACE' or @DATA8 = 'LAPSED_GRACE' or @DATA9 = 'LAPSED_GRACE' or @DATA10 = 'LAPSED_GRACE' or @DATA11 = 'LAPSED_GRACE' or @DATA12 = 'LAPSED_GRACE')
              and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[LAPSED_GRACE]) as [LAPSED_GRACE], ' 
                set @Select2SQL = @Select2SQL + '0 as [LAPSED_GRACE], ' 
                set @Select3SQL = @Select3SQL + '0 as [LAPSED_GRACE], '
                set @Select4SQL = @Select4SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 103 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cm.[FIRSTDATEDIMID] and cm.[LASTDATEDIMID] then 1 else 0 end) as [LAPSED_GRACE], '
              end      

            --Expired

              --No date groupings

            if (@DATA1 = 'EXPIRED' or @DATA2 = 'EXPIRED' or @DATA3 = 'EXPIRED' or @DATA4 = 'EXPIRED' or @DATA5 = 'EXPIRED' or @DATA6 = 'EXPIRED' or @DATA7 = 'EXPIRED' or @DATA8 = 'EXPIRED' or @DATA9 = 'EXPIRED' or @DATA10 = 'EXPIRED' or @DATA11 = 'EXPIRED' or @DATA12 = 'EXPIRED')
              and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[EXPIRED]) as [EXPIRED], ' 
                set @Select2SQL = @Select2SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 105 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between ' + @STARTDATEDIMID + ' and ' + @ENDDATEDIMID + ' then 1 else 0 end) as [EXPIRED], '
              end

              --By Year

            if (@DATA1 = 'EXPIRED' or @DATA2 = 'EXPIRED' or @DATA3 = 'EXPIRED' or @DATA4 = 'EXPIRED' or @DATA5 = 'EXPIRED' or @DATA6 = 'EXPIRED' or @DATA7 = 'EXPIRED' or @DATA8 = 'EXPIRED' or @DATA9 = 'EXPIRED' or @DATA10 = 'EXPIRED' or @DATA11 = 'EXPIRED' or @DATA12 = 'EXPIRED')
              and (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[EXPIRED]) as [EXPIRED], ' 
                set @Select2SQL = @Select2SQL + '0 as [EXPIRED], ' 
                set @Select3SQL = @Select3SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 105 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cy.[FIRSTDATEDIMID] and cy.[LASTDATEDIMID] then 1 else 0 end) as [EXPIRED], '
              end

              --By Month

            if (@DATA1 = 'EXPIRED' or @DATA2 = 'EXPIRED' or @DATA3 = 'EXPIRED' or @DATA4 = 'EXPIRED' or @DATA5 = 'EXPIRED' or @DATA6 = 'EXPIRED' or @DATA7 = 'EXPIRED' or @DATA8 = 'EXPIRED' or @DATA9 = 'EXPIRED' or @DATA10 = 'EXPIRED' or @DATA11 = 'EXPIRED' or @DATA12 = 'EXPIRED')
              and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[EXPIRED]) as [EXPIRED], ' 
                set @Select2SQL = @Select2SQL + '0 as [EXPIRED], ' 
                set @Select3SQL = @Select3SQL + '0 as [EXPIRED], '
                set @Select4SQL = @Select4SQL + 'sum(case when dma.[MEMBERSHIPACTIONCODE] = 105 and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between cm.[FIRSTDATEDIMID] and cm.[LASTDATEDIMID] then 1 else 0 end) as [EXPIRED], '
              end      

            --Active Memberships

              --No date groupings

            if (@DATA1 = 'MEMBERSHIPS' or @DATA2 = 'MEMBERSHIPS' or @DATA3 = 'MEMBERSHIPS' or @DATA4 = 'MEMBERSHIPS' or @DATA5 = 'MEMBERSHIPS' or @DATA6 = 'MEMBERSHIPS' or @DATA7 = 'MEMBERSHIPS' or @DATA8 = 'MEMBERSHIPS' or @DATA9 = 'MEMBERSHIPS' or @DATA10 = 'MEMBERSHIPS' or @DATA11 = 'MEMBERSHIPS' or @DATA12 = 'MEMBERSHIPS')
              and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[MEMBERSHIPS]) as [MEMBERSHIPS], ' 
                set @Select2SQL = @Select2SQL + 'max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= ' + @ENDDATEDIMID + ' and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] >= ' + @STARTDATEDIMID + ' then 1
            else 0 
          end
        ) as [MEMBERSHIPS], '
              end

              --By Year

            if (@DATA1 = 'MEMBERSHIPS' or @DATA2 = 'MEMBERSHIPS' or @DATA3 = 'MEMBERSHIPS' or @DATA4 = 'MEMBERSHIPS' or @DATA5 = 'MEMBERSHIPS' or @DATA6 = 'MEMBERSHIPS' or @DATA7 = 'MEMBERSHIPS' or @DATA8 = 'MEMBERSHIPS' or @DATA9 = 'MEMBERSHIPS' or @DATA10 = 'MEMBERSHIPS' or @DATA11 = 'MEMBERSHIPS' or @DATA12 = 'MEMBERSHIPS')
              and (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[MEMBERSHIPS]) as [MEMBERSHIPS], sum(m.[ACTIVEMEMBERSHIPSBYYEAR]) as [ACTIVEMEMBERSHIPSBYYEAR], ' 
                set @Select2SQL = @Select2SQL + 'max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= ' + @ENDDATEDIMID + ' and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] >= ' + @STARTDATEDIMID + ' then 1
            else 0 
          end
        ) as [MEMBERSHIPS], 0 as [ACTIVEMEMBERSHIPSBYYEAR], '
                set @Select3SQL = @Select3SQL + '0 as [MEMBERSHIPS],
        max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= cy.[LASTDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] >= cy.[FIRSTDATEDIMID] then 1 
            else 0 
          end
        ) as [ACTIVEMEMBERSHIPSBYYEAR], '
              end

              --By Month

            if (@DATA1 = 'MEMBERSHIPS' or @DATA2 = 'MEMBERSHIPS' or @DATA3 = 'MEMBERSHIPS' or @DATA4 = 'MEMBERSHIPS' or @DATA5 = 'MEMBERSHIPS' or @DATA6 = 'MEMBERSHIPS' or @DATA7 = 'MEMBERSHIPS' or @DATA8 = 'MEMBERSHIPS' or @DATA9 = 'MEMBERSHIPS' or @DATA10 = 'MEMBERSHIPS' or @DATA11 = 'MEMBERSHIPS' or @DATA12 = 'MEMBERSHIPS')
              and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + 'sum(m.[MEMBERSHIPS]) as [MEMBERSHIPS], sum(m.[ACTIVEMEMBERSHIPSBYYEAR]) as [ACTIVEMEMBERSHIPSBYYEAR], sum(m.[ACTIVEMEMBERSHIPSBYMONTH]) as [ACTIVEMEMBERSHIPSBYMONTH], ' 
                set @Select2SQL = @Select2SQL + 'max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= ' + @ENDDATEDIMID + ' and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] >= ' + @STARTDATEDIMID + ' then 1
            else 0 
          end
        ) as [MEMBERSHIPS], 0 as [ACTIVEMEMBERSHIPSBYYEAR], 0 as [ACTIVEMEMBERSHIPSBYMONTH], '
                set @Select3SQL = @Select3SQL + '0 as [MEMBERSHIPS],
        max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= cy.[LASTDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] >= cy.[FIRSTDATEDIMID] then 1 
            else 0 
          end
        ) as [ACTIVEMEMBERSHIPSBYYEAR], 0 as [ACTIVEMEMBERSHIPSBYMONTH], '
                set @Select4SQL = @Select4SQL + '0 as [MEMBERSHIPS],
        0 as [ACTIVEMEMBERSHIPSBYYEAR],
        max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= cm.[LASTDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] >= cm.[FIRSTDATEDIMID] then 1 
            else 0 
          end
        ) as [ACTIVEMEMBERSHIPSBYMONTH], '
              end      

            --Retention Rate

              --No date groupings

            if (@DATA1 = 'RETENTIONRATE' or @DATA2 = 'RETENTIONRATE' or @DATA3 = 'RETENTIONRATE' or @DATA4 = 'RETENTIONRATE' or @DATA5 = 'RETENTIONRATE' or @DATA6 = 'RETENTIONRATE' or @DATA7 = 'RETENTIONRATE' or @DATA8 = 'RETENTIONRATE' or @DATA9 = 'RETENTIONRATE' or @DATA10 = 'RETENTIONRATE' or @DATA11 = 'RETENTIONRATE' or @DATA12 = 'RETENTIONRATE')
              and (@GROUP1 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP2 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME') and @GROUP3 not in ('CALENDARYEAR', 'FISCALYEAR', 'CALENDARMONTHYEARNAME'))

              begin
                set @Select1SQL = @Select1SQL + '0 as [RETENTIONRATE], sum(m.[MEMBERSHIPSSTART]) as [MEMBERSHIPSSTART], 
        sum(m.[MEMBERSHIPSEND]) as [MEMBERSHIPSEND], ' 
                set @Select2SQL = @Select2SQL + 'max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and ' + @STARTDATEDIMID + ' between fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] then 1 
            else 0 
          end
        ) as [MEMBERSHIPSSTART],
        max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and ' + @ENDDATEDIMID + ' between fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] then 1 
            else 0 
          end
        ) as [MEMBERSHIPSEND], '
              end

              --By Year

            if (@DATA1 = 'RETENTIONRATE' or @DATA2 = 'RETENTIONRATE' or @DATA3 = 'RETENTIONRATE' or @DATA4 = 'RETENTIONRATE' or @DATA5 = 'RETENTIONRATE' or @DATA6 = 'RETENTIONRATE' or @DATA7 = 'RETENTIONRATE' or @DATA8 = 'RETENTIONRATE' or @DATA9 = 'RETENTIONRATE' or @DATA10 = 'RETENTIONRATE' or @DATA11 = 'RETENTIONRATE' or @DATA12 = 'RETENTIONRATE')
              and (@GROUP1 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP2 in ('CALENDARYEAR', 'FISCALYEAR') or @GROUP3 in ('CALENDARYEAR', 'FISCALYEAR'))
              and (@GROUP1 <> 'CALENDARMONTHYEARNAME' and @GROUP2 <> 'CALENDARMONTHYEARNAME' and @GROUP3 <> 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + '0 as [RETENTIONRATE], sum(m.[MEMBERSHIPSSTART]) as [MEMBERSHIPSSTART], 
        sum(m.[MEMBERSHIPSEND]) as [MEMBERSHIPSEND], 
        sum(m.[MEMBERSHIPSSTARTBYYEAR]) as [MEMBERSHIPSSTARTBYYEAR], 
        sum(m.[MEMBERSHIPSENDBYYEAR]) as [MEMBERSHIPSENDBYYEAR], ' 
                set @Select2SQL = @Select2SQL + 'max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and ' + @STARTDATEDIMID + ' between fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] then 1 
            else 0 
          end
        ) as [MEMBERSHIPSSTART],
        max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and ' + @ENDDATEDIMID + ' between fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] then 1 
            else 0 
          end
        ) as [MEMBERSHIPSEND], 
        0 as [MEMBERSHIPSSTARTBYYEAR],
        0 as [MEMBERSHIPSENDBYYEAR], '        
                set @Select3SQL = @Select3SQL + '0 as [MEMBERSHIPSSTART],
        0 as [MEMBERSHPSEND],
        max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and cy.[FIRSTDATEDIMID] between fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] then 1 
            else 0 
          end
        ) as [MEMBERSHIPSSTARTBYYEAR],
        max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and cy.[LASTDATEDIMID] between fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] then 1 
            else 0 
          end
        ) as [MEMBERSHIPSENDBYYEAR], '
              end

              --By Month

            if (@DATA1 = 'RETENTIONRATE' or @DATA2 = 'RETENTIONRATE' or @DATA3 = 'RETENTIONRATE' or @DATA4 = 'RETENTIONRATE' or @DATA5 = 'RETENTIONRATE' or @DATA6 = 'RETENTIONRATE' or @DATA7 = 'RETENTIONRATE' or @DATA8 = 'RETENTIONRATE' or @DATA9 = 'RETENTIONRATE' or @DATA10 = 'RETENTIONRATE' or @DATA11 = 'RETENTIONRATE' or @DATA12 = 'RETENTIONRATE')
              and (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')

              begin
                set @Select1SQL = @Select1SQL + '0 as [RETENTIONRATE], sum(m.[MEMBERSHIPSSTART]) as [MEMBERSHIPSSTART], 
        sum(m.[MEMBERSHIPSEND]) as [MEMBERSHIPSEND], 
        sum(m.[MEMBERSHIPSSTARTBYYEAR]) as [MEMBERSHIPSSTARTBYYEAR], 
        sum(m.[MEMBERSHIPSENDBYYEAR]) as [MEMBERSHIPSENDBYYEAR], 
        sum(m.[MEMBERSHIPSSTARTBYMONTH]) as [MEMBERSHIPSSTARTBYMONTH], 
        sum(m.[MEMBERSHIPSENDBYMONTH]) as [MEMBERSHIPSENDBYMONTH] ' 
                set @Select2SQL = @Select2SQL + 'max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and ' + @STARTDATEDIMID + ' between fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] then 1 
            else 0 
          end
        ) as [MEMBERSHIPSSTART],
        max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and ' + @ENDDATEDIMID + ' between fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] then 1 
            else 0 
          end
        ) as [MEMBERSHIPSEND], 
        0 as [MEMBERSHIPSSTARTBYYEAR],
        0 as [MEMBERSHIPSENDBYYEAR],
        0 as [MEMBERSHIPSSTARTBYMONTH],
        0 as [MEMBERSHIPSENDBYMONTH], '        
                set @Select3SQL = @Select3SQL + '0 as [MEMBERSHIPSSTART],
        0 as [MEMBERSHPSEND],
        max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and cy.[FIRSTDATEDIMID] between fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] then 1 
            else 0 
          end
        ) as [MEMBERSHIPSSTARTBYYEAR],
        max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and cy.[LASTDATEDIMID] between fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] then 1 
            else 0 
          end
        ) as [MEMBERSHIPSENDBYYEAR], 
        0 as [MEMBERSHIPSSTARTBYMONTH],
        0 as [MEMBERSHIPSENDBYMONTH], '
              set @Select4SQL = @Select4SQL + '0 as [MEMBERSHIPSSTART],
        0 as [MEMBERSHPSEND],
        0 as [MEMBERSHIPSSTARTBYYEAR],
        0 as [MEMBERSHIPSENDBYYEAR],
        max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and cm.[FIRSTDATEDIMID] between fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] then 1 
            else 0 
          end
        ) as [MEMBERSHIPSSTARTBYMONTH],
        max(
          case 
            when ms.[MEMBERSHIPSTATUSCODE] in (0, 99) and cm.[LASTDATEDIMID] between fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] and fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] then 1 
            else 0 
          end
        ) as [MEMBERSHIPSENDBYMONTH] '
              end      

            --Clean up any ending commas

            if right(@Select1SQL, 2) = ', '
              set @Select1SQL = left(@Select1SQL, len(@Select1SQL) - 1) + ' ' 

            if right(@Select2SQL, 2) = ', '
              set @Select2SQL = left(@Select2SQL, len(@Select2SQL) - 1) + ' ' 

            if right(@Group2SQL, 2) = ', '
              set @Group2SQL = left(@Group2SQL, len(@Group2SQL) - 1) + ' ' 

            if right(@Select3SQL, 2) = ', '
              set @Select3SQL = left(@Select3SQL, len(@Select3SQL) - 1) + ' ' 

            if right(@Group3SQL, 2) = ', '
              set @Group3SQL = left(@Group3SQL, len(@Group3SQL) - 1) + ' ' 

            if right(@Group1SQL, 2) = ', '
              set @Group1SQL = left(@Group1SQL, len(@Group1SQL) - 1) + ' ' 

            if right(@Select4SQL, 2) = ', '
              set @Select4SQL = left(@Select4SQL, len(@Select4SQL) - 1) + ' ' 

            if right(@Group4SQL, 2) = ', '
              set @Group4SQL = left(@Group4SQL, len(@Group4SQL) - 1) + ' ' 

            --Clean up nulls

            if @Select1SQL is null
            set @Select1SQL = ''
            if @Select2SQL is null
              set @Select2SQL = ''
            if @Select3SQL is null
              set @Select3SQL = ''
            if @Select4SQL is null
              set @Select4SQL = ''
            if @Group1SQL is null
              set @Group1SQL = ''
            if @Group2SQL is null
              set @Group2SQL = ''
            if @Group3SQL is null
              set @Group3SQL = ''
            if @Group4SQL is null
              set @Group4SQL = ''
            if @From1SQL is null
              set @From1SQL = ''
            if @From2SQL is null
              set @From2SQL = ''
            if @From3SQL is null
              set @From3SQL = ''
            if @From4SQL is null
              set @From4SQL = ''
            if @Where1SQL is null
              set @Where1SQL = ''
            if @Where2SQL is null
              set @Where2SQL = ''
            if @Where3SQL is null
              set @Where3SQL = ''
            if @Where4SQL is null
              set @Where4SQL = ''

            --Remove Groupings if no values

            if len(@Group1SQL) < 12
              set @Group1SQL = ''
            if len(@Group2SQL) < 12
              set @Group2SQL = ''
            if len(@Group3SQL) < 12
              set @Group3SQL = ''
            if len(@Group4SQL) < 12
              set @Group4SQL = ''

            --Put it all together

            set @SQL = @TableVarSQL + @CTESQL + @Select1SQL + @From1SQL + @Select2SQL + @From2SQL + @Where2SQL + @Group2SQL + @Select3SQL + @From3SQL + @Where3SQL + @Group3SQL + @Select4SQL + @From4SQL + @Where4SQL + @Group4SQL+ @Where1SQL + @Group1SQL

            --select @SQL

            execute sp_executesql @SQL, N'@PROGRAMFILTERTYPE nvarchar(100)', @PROGRAMFILTERTYPE = @PROGRAMFILTERTYPE