USP_REPORT_MEMBERSHIPACTUALS

        create procedure BBDW.[USP_REPORT_MEMBERSHIPACTUALS] (
        @GROUP1 nvarchar(30),
        @GROUP2 nvarchar(30),
        @GROUP3 nvarchar(30),
        @STARTDATE1 datetime,
        @ENDDATE1 datetime,
        @STARTDATE2 datetime,
        @ENDDATE2 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;', '>');

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

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

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

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

        declare @STARTDATE1DIMID nvarchar(30)
        declare @ENDDATE1DIMID nvarchar(30)
        declare @STARTDATE2DIMID nvarchar(30)
        declare @ENDDATE2DIMID nvarchar(30)

        set @STARTDATE1DIMID = (year(@STARTDATE1) * 10000) + (month(@STARTDATE1) * 100) + day(@STARTDATE1)
        set @ENDDATE1DIMID = (year(@ENDDATE1) * 10000) + (month(@ENDDATE1) * 100) + day(@ENDDATE1)
        set @STARTDATE2DIMID = (year(@STARTDATE2) * 10000) + (month(@STARTDATE2) * 100) + day(@STARTDATE2)
        set @ENDDATE2DIMID = (year(@ENDDATE2) * 10000) + (month(@ENDDATE2) * 100) + day(@ENDDATE2)

        declare @SQL 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) = ''

        --Build core SQL

        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

        set @Select1SQL = 'select @PROGRAMFILTERTYPE as [PROGRAMFILTERTYPE], 
        sum(m.[EARNEDREVENUE1]) as [EARNEDREVENUE1],
        sum(m.[CONTRIBUTEDREVENUE1]) as [CONTRIBUTEDREVENUE1],
        sum(m.[EARNEDREVENUE2]) as [EARNEDREVENUE2],
        sum(m.[CONTRIBUTEDREVENUE2]) as [CONTRIBUTEDREVENUE2],
        sum(m.[TRANSACTIONS1]) as [TRANSACTIONS1],
        sum(m.[TRANSACTIONS2]) as [TRANSACTIONS2], '

        set @From1SQL = 'from ('

        set @Select2SQL = 'select
        case when ((rc.[REVENUEAPPLICATIONCODE] = 5 and rc.[REVENUEAPPLICATIONTYPECODE] = 2) or (rc.[REVENUEAPPLICATIONCODE] = 18 and rc.[REVENUEAPPLICATIONTYPECODE] = 18))
        then sum(r.[REVENUEAPPLICATIONAMOUNT]) else 0 end as [EARNEDREVENUE1],
        case when (rc.[REVENUEAPPLICATIONCODE] = 0 and rc.[REVENUEAPPLICATIONTYPECODE] = 0)
        then sum(r.[REVENUEAPPLICATIONAMOUNT]) else 0 end as [CONTRIBUTEDREVENUE1],
        0 as [EARNEDREVENUE2],
        0 as [CONTRIBUTEDREVENUE2],
        count(distinct mr.[MEMBERSHIPACTIONDIMID]) as [TRANSACTIONS1],
        0 as [TRANSACTIONS2], '

        set @From2SQL = 'from BBDW.[v_FACT_MEMBERSHIPREVENUE] mr
        left join BBDW.[v_FACT_REVENUE] r on mr.[REVENUEFACTID] = r.[REVENUEFACTID]
        left join BBDW.[v_DIM_REVENUECODE] rc on r.[REVENUECODEDIMID] = rc.[REVENUECODEDIMID]
        left join BBDW.[v_DIM_MEMBERSHIPPROGRAM] mp on mr.[MEMBERSHIPPROGRAMDIMID] = mp.[MEMBERSHIPPROGRAMDIMID]
        inner join @MEMBERSHIPPROGRAMIDTABLE tmp on tmp.MEMBERSHIPPROGRAMSYSTEMID = mp.[MEMBERSHIPPROGRAMSYSTEMID] '

        set @Where2SQL = 'where r.[REVENUEDATEDIMID] between ' + @STARTDATE1DIMID + ' and ' + @ENDDATE1DIMID 

        set @Group2SQL = 'group by rc.[REVENUEAPPLICATIONTYPECODE], rc.[REVENUEAPPLICATIONCODE], '


        set @Select3SQL = ' union all select
        0 as [EARNEDREVENUE1],
        0 as [CONTRIBUTEDREVENUE1],
        case when ((rc.[REVENUEAPPLICATIONCODE] = 5 and rc.[REVENUEAPPLICATIONTYPECODE] = 2) or (rc.[REVENUEAPPLICATIONCODE] = 18 and rc.[REVENUEAPPLICATIONTYPECODE] = 18))
        then sum(r.[REVENUEAPPLICATIONAMOUNT]) else 0 end as [EARNEDREVENUE2],
        case when (rc.[REVENUEAPPLICATIONCODE] = 0 and rc.[REVENUEAPPLICATIONTYPECODE] = 0)
        then sum(r.[REVENUEAPPLICATIONAMOUNT]) else 0 end as [CONTRIBUTEDREVENUE2],
        0 as [TRANSACTIONS1],
        count(distinct mr.[MEMBERSHIPACTIONDIMID]) as [TRANSACTIONS2], '

        set @From3SQL = 'from BBDW.[v_FACT_MEMBERSHIPREVENUE] mr
        left join BBDW.[v_FACT_REVENUE] r on mr.[REVENUEFACTID] = r.[REVENUEFACTID]
        left join BBDW.[v_DIM_REVENUECODE] rc on r.[REVENUECODEDIMID] = rc.[REVENUECODEDIMID]
        left join BBDW.[v_DIM_MEMBERSHIPPROGRAM] mp on mr.[MEMBERSHIPPROGRAMDIMID] = mp.[MEMBERSHIPPROGRAMDIMID]
        inner join @MEMBERSHIPPROGRAMIDTABLE tmp on tmp.MEMBERSHIPPROGRAMSYSTEMID = mp.[MEMBERSHIPPROGRAMSYSTEMID] '

        set @Where3SQL = 'where r.[REVENUEDATEDIMID] between ' + @STARTDATE2DIMID + ' and ' + @ENDDATE2DIMID

        set @Group3SQL = 'group by
        rc.[REVENUEAPPLICATIONTYPECODE],
        rc.[REVENUEAPPLICATIONCODE], '

        set @Where1SQL = ') m '

        set @Group1SQL = 'group by '

        --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_DIM_CONSTITUENTLOOKUP] cl on r.[CONSTITUENTDIMID] = cl.[CONSTITUENTDIMID] '
        set @From3SQL = @From3SQL + 'left join BBDW.[v_DIM_CONSTITUENTLOOKUP] cl on r.[CONSTITUENTDIMID] = cl.[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] = r.[CONSTITUENTDIMID] and de.[ISPRIMARYRECORD] = 1 and de.[EDUCATIONINSTITUTIONISAFFLIATED] = 1 '
        set @From3SQL = @From3SQL + 'left join BBDW.[v_DIM_EDUCATION] de on de.[CONSTITUENTDIMID] = r.[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] = r.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] = r.CONSTITUENTDIMID
        left join BBDW.[v_DIM_ETHNICITY] e on fe.[ETHNICITYDIMID] = e.[ETHNICITYDIMID]  '
        end

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

        begin
        set @From2SQL = @From2SQL + 'left join BBDW.[v_DIM_DATE] d on r.[REVENUEDATEDIMID] = d.[DATEDIMID] '
        set @From3SQL = @From3SQL + 'left join BBDW.[v_DIM_DATE] d on r.[REVENUEDATEDIMID] = d.[DATEDIMID] '
        end

        --Add selected groupings

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

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

        if @GROUP1 = 'MEMBERSHIPREPORTINGGROUP' or @GROUP2 = 'MEMBERSHIPREPORTINGGROUP' or @GROUP3 = 'MEMBERSHIPREPORTINGGROUP'
        begin
        set @Select1SQL = @Select1SQL + 'm.[MEMBERSHIPREPORTINGGROUP], '
        set @Select2SQL = @Select2SQL + 'mp.[MEMBERSHIPREPORTINGGROUP], '
        set @Select3SQL = @Select3SQL + 'mp.[MEMBERSHIPREPORTINGGROUP], '
        set @Group1SQL = @Group1SQL + 'm.[MEMBERSHIPREPORTINGGROUP], '
        set @Group2SQL = @Group2SQL + 'mp.[MEMBERSHIPREPORTINGGROUP], '
        set @Group3SQL = @Group3SQL + 'mp.[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 @Group1SQL = @Group1SQL + 'm.[CLASSOF], '
        set @Group2SQL = @Group2SQL + 'isnull(de.[CLASSOF], 0), '
        set @Group3SQL = @Group3SQL + '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 @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, '
        end

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

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

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

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

        if @GROUP1 = 'POSTCODE' or @GROUP2 = 'POSTCODE' or @GROUP3 = 'POSTCODE'
        begin
        set @Select1SQL = @Select1SQL + 'm.[POSTCODE], '
        set @Select2SQL = @Select2SQL + 'cl.[PRIMARYADDRESSPOSTCODE] as [POSTCODE], '
        set @Select3SQL = @Select3SQL + 'cl.[PRIMARYADDRESSPOSTCODE] as [POSTCODE], '
        set @Group1SQL = @Group1SQL + 'm.[POSTCODE], '
        set @Group2SQL = @Group2SQL + 'cl.[PRIMARYADDRESSPOSTCODE], '
        set @Group3SQL = @Group3SQL + 'cl.[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 @Group1SQL = @Group1SQL + 'm.[ETHNICITY], '
        set @Group2SQL = @Group2SQL + 'isnull(e.[ETHNICITY], ''No Ethnicity''), '
        set @Group3SQL = @Group3SQL + '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 + 'd.[FISCALYEAR], d.[CALENDARYEAR], '
            set @Select3SQL = @Select3SQL + 'd.[FISCALYEAR], d.[CALENDARYEAR], '
            set @Group1SQL = @Group1SQL + 'm.[FISCALYEAR], m.[CALENDARYEAR], '
            set @Group2SQL = @Group2SQL + 'd.[FISCALYEAR], d.[CALENDARYEAR], '
            set @Group3SQL = @Group3SQL + 'd.[FISCALYEAR], d.[CALENDARYEAR], '
          end

        if (@GROUP1 = 'CALENDARMONTHYEARNAME' or @GROUP2 = 'CALENDARMONTHYEARNAME' or @GROUP3 = 'CALENDARMONTHYEARNAME')
          begin
            set @Select1SQL = @Select1SQL + 'm.[FISCALYEAR], m.[CALENDARMONTHYEARNAME], m.[CALENDARYEAR], m.[CALENDARMONTH], '
            set @Select2SQL = @Select2SQL + 'd.[FISCALYEAR], d.[CALENDARMONTHYEARNAME], d.[CALENDARYEAR], d.[CALENDARMONTH], '
            set @Select3SQL = @Select3SQL + 'd.[FISCALYEAR], d.[CALENDARMONTHYEARNAME], d.[CALENDARYEAR], d.[CALENDARMONTH], '
            set @Group1SQL = @Group1SQL + 'm.[FISCALYEAR], m.[CALENDARMONTHYEARNAME], m.[CALENDARYEAR], m.[CALENDARMONTH], '
            set @Group2SQL = @Group2SQL + 'd.[FISCALYEAR], d.[CALENDARMONTHYEARNAME], d.[CALENDARYEAR], d.[CALENDARMONTH], '
            set @Group3SQL = @Group3SQL + 'd.[FISCALYEAR], d.[CALENDARMONTHYEARNAME], d.[CALENDARYEAR], d.[CALENDARMONTH], '
          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) + ' '

        --Remove Groupings if no values

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

      --Put it all together

      set @SQL = @TableVarSQL + @Select1SQL + @From1SQL + @Select2SQL + @From2SQL + @Where2SQL + @Group2SQL + @Select3SQL + @From3SQL + @Where3SQL + @Group3SQL + @Where1SQL + @Group1SQL

      --select @SQL

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