USP_CONSTITUENTSMARTFIELD_HH_REVENUESPLITANDRECOGNITIONS

            CREATE procedure dbo.USP_CONSTITUENTSMARTFIELD_HH_REVENUESPLITANDRECOGNITIONS
            (
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @CALCULATIONTYPE tinyint,
                @CALCULATEDREVENUE tinyint,
                @ASOF datetime,                
                @DATETYPECODE tinyint = null,
                @STARTDATERECENTDATEUNITCODE tinyint = null,
                @STARTDATERECENTDATEINTERVAL int = null,        
                @REVENUETYPECODES xml = null,
                @SELECTIONS xml = null,                
                @REVENUEAPPLICATIONSELECTION uniqueidentifier = null,
                @RECOGNITIONTYPES xml = null,
                @INCLUDEHISTORICALDATA bit = null,
                @CURRENCYID uniqueidentifier = null,
                @SMARTFIELDID uniqueidentifier = null,
                @SITES xml = null
            )
            as
            set nocount on;

            --If the date type is 'Recent dates', then all constituents need to be processed; so set the @ASOF date to null before passing it 

            --to the data function

            if @DATETYPECODE = 1 and @STARTDATERECENTDATEINTERVAL is not null
                set @ASOF = null;

            if @CURRENCYID is null
                set @CURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

            /* Calculate start and end dates */
            declare @CURRENTDATEEARLIESTTIME datetime;
            set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

            declare @COMPUTEDSTARTDATE datetime;
            declare @COMPUTEDENDDATE datetime;

            if @DATETYPECODE = 0
            begin        
                set @COMPUTEDSTARTDATE = @STARTDATE;
                set @COMPUTEDENDDATE = @ENDDATE;
            end
            else if @DATETYPECODE = 1
            begin
                set @COMPUTEDSTARTDATE = 
                case @STARTDATERECENTDATEUNITCODE
                    when 0 then dateadd(year, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
                    when 1 then dateadd(quarter, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
                    when 2 then dateadd(month, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
                    when 3 then dateadd(week, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
                    when 4 then dateadd(day, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
                end;
                set @COMPUTEDENDDATE = null;
            end

      set @COMPUTEDSTARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@COMPUTEDSTARTDATE);
      set @COMPUTEDENDDATE = dbo.UFN_DATE_GETLATESTTIME(@COMPUTEDENDDATE);

      if object_id('tempdb..#TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS') is not null
          drop table #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS

            create table #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS 
            (
                RECORDID uniqueidentifier,
                MEMBERID uniqueidentifier,
                HOUSEHOLDID uniqueidentifier,
                GROUPINGID uniqueidentifier,
                ISHISTORICAL bit,
                AMOUNT money,
                DATE datetime,
                DATEADDED datetime
            )

      if object_id('tempdb..#TMP_MEMBERS_HH_REVENUESPLITANDRECOGNITIONS') is not null
          drop table #TMP_MEMBERS_HH_REVENUESPLITANDRECOGNITIONS

            create table #TMP_MEMBERS_HH_REVENUESPLITANDRECOGNITIONS
            (
                MEMBERID uniqueidentifier,
                HOUSEHOLDID uniqueidentifier
            );

            insert into #TMP_MEMBERS_HH_REVENUESPLITANDRECOGNITIONS(MEMBERID, HOUSEHOLDID)
                select
                    CONSTITUENT.ID,
                    GROUPMEMBER.GROUPID
                from dbo.CONSTITUENT
        --if GROUPDATA.ID cannot be null shouldn't this be an inner join

                --left join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENT.ID  

        --left join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID and GROUPDATA.GROUPTYPECODE = 0  

        --where not GROUPDATA.ID is null;  

        inner join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENT.ID  
        inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID 
        where GROUPDATA.GROUPTYPECODE = 0 

        create index IX_TMP_MEMBERS_HH_REVENUESPLITANDRECOGNITIONS_MEMBERID on #TMP_MEMBERS_HH_REVENUESPLITANDRECOGNITIONS (MEMBERID)
        create index IX_TMP_MEMBERS_HH_REVENUESPLITANDRECOGNITIONS_HOUSEHOLDID on #TMP_MEMBERS_HH_REVENUESPLITANDRECOGNITIONS (HOUSEHOLDID)

            if @CALCULATEDREVENUE = 0 or @CALCULATEDREVENUE = 2 --Revenue

            begin
                if object_id('tempdb..#TMP_GROUP_REVENUESPLITS') is not null
            drop table #TMP_GROUP_REVENUESPLITS 

          create table #TMP_GROUP_REVENUESPLITS
                (
                    GROUPID uniqueidentifier,
                    GROUPTYPECODE tinyint,
                    GROUPMEMBERID uniqueidentifier,
                    ISGROUP bit,
                    GROUPMEMBERSHIPSTARTDATE datetime,
                    GROUPMEMBERSHIPENDDATE datetime,
                    GROUPMEMBERSHIPISHISTORICAL bit,
                    REVENUEID uniqueidentifier,
                    REVENUESPLITID uniqueidentifier,
                    REVENUEDATE datetime,
                    REVENUESPLITAMOUNT money,
                    REVENUEDATEADDED datetime,
                    TSLONG bigint
                );

              exec dbo.USP_GROUP_REVENUESPLITS null, @REVENUETYPECODES, null, null, 1, @ASOF, 1, null, @SELECTIONS, @REVENUEAPPLICATIONSELECTION, @CURRENCYID, @SITES;

        --First gather the data for members

                insert into #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS(RECORDID, MEMBERID, HOUSEHOLDID, GROUPINGID, ISHISTORICAL, AMOUNT, DATE, DATEADDED)
                    select distinct
                        [DATA].REVENUESPLITID,
                        [MEMBERS].MEMBERID,
                        [DATA].GROUPID,
                        [MEMBERS].MEMBERID,
                        [DATA].GROUPMEMBERSHIPISHISTORICAL,
                        [DATA].REVENUESPLITAMOUNT,
                        [DATA].REVENUEDATE,
                        [DATA].REVENUEDATEADDED
                    from #TMP_MEMBERS_HH_REVENUESPLITANDRECOGNITIONS as [MEMBERS]
                    left join #TMP_GROUP_REVENUESPLITS as [DATA] on [DATA].GROUPID = [MEMBERS].HOUSEHOLDID
                    where (@COMPUTEDSTARTDATE is null or [DATA].REVENUEDATE >= @COMPUTEDSTARTDATE)
                        and (@COMPUTEDENDDATE is null or [DATA].REVENUEDATE <= @COMPUTEDENDDATE)
                        and ((@INCLUDEHISTORICALDATA = 0 and [DATA].GROUPMEMBERSHIPISHISTORICAL = 0) or (@INCLUDEHISTORICALDATA = 1) or [DATA].GROUPMEMBERSHIPISHISTORICAL is null);

                --Second gather the data for households and individuals not in a household            

        ;with CTE_DATA as
        (select 
            GROUPID,
            GROUPMEMBERID,
            REVENUESPLITID,
            REVENUESPLITAMOUNT,  
            REVENUEDATE,  
            REVENUEDATEADDED
        from #TMP_GROUP_REVENUESPLITS
        where (@COMPUTEDSTARTDATE is null or REVENUEDATE >= @COMPUTEDSTARTDATE)  
              and (@COMPUTEDENDDATE is null or REVENUEDATE <= @COMPUTEDENDDATE)  
              and ((@INCLUDEHISTORICALDATA = 0 and GROUPMEMBERSHIPISHISTORICAL = 0) or (@INCLUDEHISTORICALDATA = 1) or GROUPMEMBERSHIPISHISTORICAL is null)  
        )
        insert into #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS(RECORDID, MEMBERID, HOUSEHOLDID, GROUPINGID, ISHISTORICAL, AMOUNT, DATE, DATEADDED)  
             select distinct 
              coalesce([DATA_GROUP].REVENUESPLITID, [DATA_MEMBER].REVENUESPLITID),
              CONSTITUENT.ID,  
              null,  
              CONSTITUENT.ID,  
              0,  
              coalesce([DATA_GROUP].REVENUESPLITAMOUNT, [DATA_MEMBER].REVENUESPLITAMOUNT),
              coalesce([DATA_GROUP].REVENUEDATE, [DATA_MEMBER].REVENUEDATE),
              coalesce([DATA_GROUP].REVENUEDATEADDED, [DATA_MEMBER].REVENUEDATEADDED)
             from dbo.CONSTITUENT  
             left join CTE_DATA as [DATA_GROUP] on [DATA_GROUP].GROUPID = CONSTITUENT.ID
             left join CTE_DATA as [DATA_MEMBER] on [DATA_MEMBER].GROUPMEMBERID = CONSTITUENT.ID
             where 
             CONSTITUENT.ID not in (select GROUPMEMBER.MEMBERID from dbo.GROUPMEMBER
                                    inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
                                    inner join dbo.CONSTITUENTHOUSEHOLD CHH on CHH.ID = GROUPMEMBER.MEMBERID
                                    where GROUPDATA.GROUPTYPECODE = 0  and HOUSEHOLDID is not NULL)

        create index IX_TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS_GROUPINGID on #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS (GROUPINGID)
        create index IX_TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS_RECORDID on #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS (RECORDID)

                --Third, gather the data for constituents who do not have any gifts in the @CALCULATEDSTARTDATE/ENDDATE range. This will put $0 on their smart field value

                --This fixes WI60704

                insert into #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS(RECORDID, MEMBERID, HOUSEHOLDID, GROUPINGID, ISHISTORICAL, AMOUNT, DATE, DATEADDED)
                    select
                        null,
                        CONSTITUENT.ID,
                        null,
                        CONSTITUENT.ID,
                        0,
                        0,
                        null,
                        null
                    from dbo.CONSTITUENT 
                    inner join #TMP_GROUP_REVENUESPLITS as [REVENUEFUNCTIONDATA] on [REVENUEFUNCTIONDATA].GROUPMEMBERID = CONSTITUENT.ID
                    --left join #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA] on [DATA].GROUPINGID = CONSTITUENT.ID

                    where --[DATA].GROUPINGID is null

          not exists (select 1 from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA] where [DATA].GROUPINGID = CONSTITUENT.ID)

                --Fourth, gather data for members who do not have any gifts yet. This will put $o on their smart field value 

                --This fixes WI75799


                insert into #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS(RECORDID, MEMBERID, HOUSEHOLDID, GROUPINGID, ISHISTORICAL, AMOUNT, DATE, DATEADDED)
                    select
                        null,
                        [MEMBERS].MEMBERID,
                        null,
                        [MEMBERS].MEMBERID,
                        0,
                        0,
                        null,
                        null
                    from #TMP_MEMBERS_HH_REVENUESPLITANDRECOGNITIONS as [MEMBERS]
                    left join #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA] on [DATA].GROUPINGID = [MEMBERS].MEMBERID
                    where [DATA].GROUPINGID is null

            end
            else --Recognition

            begin
                if object_id('tempdb..#TMP_GROUP_RECOGNITIONCREDITS') is not null
                    drop table #TMP_GROUP_RECOGNITIONCREDITS

                create table #TMP_GROUP_RECOGNITIONCREDITS
                (
                    GROUPID uniqueidentifier,
                    GROUPTYPECODE tinyint,
                    GROUPMEMBERID uniqueidentifier,
                    ISGROUP bit,
                    GROUPMEMBERSHIPSTARTDATE datetime,
                    GROUPMEMBERSHIPENDDATE datetime,
                    GROUPMEMBERSHIPISHISTORICAL bit,
                    RECOGNITIONID uniqueidentifier,
                    REVENUEID uniqueidentifier,
                    REVENUESPLITID uniqueidentifier,
                    REVENUEDATE datetime,
                    REVENUEDATEADDED datetime,
                    REVENUECONSTITUENTID uniqueidentifier,
                    RECOGNITIONDATE datetime,
                    REVENUERECOGNITIONTYPECODEID uniqueidentifier,
                    RECOGNITIONAMOUNT money,
                    RECOGNITIONDATEADDED datetime,
                    TSLONG bigint
                );

                --insert into #TMP_GROUP_RECOGNITIONCREDITS(GROUPID, GROUPTYPECODE, GROUPMEMBERID, ISGROUP, GROUPMEMBERSHIPSTARTDATE, GROUPMEMBERSHIPENDDATE, GROUPMEMBERSHIPISHISTORICAL, RECOGNITIONID, REVENUEID, REVENUESPLITID,

                --    REVENUEDATE, REVENUEDATEADDED, REVENUECONSTITUENTID, RECOGNITIONDATE, REVENUERECOGNITIONTYPECODEID, RECOGNITIONAMOUNT, RECOGNITIONDATEADDED)

                exec dbo.USP_GROUP_RECOGNITIONCREDITS null, @REVENUETYPECODES, null, null, @RECOGNITIONTYPES, 1, @ASOF, 1, null, @SELECTIONS, @REVENUEAPPLICATIONSELECTION, @CURRENCYID, @SITES;

          --select GROUPID, GROUPTYPECODE, GROUPMEMBERID, ISGROUP, GROUPMEMBERSHIPSTARTDATE, GROUPMEMBERSHIPENDDATE, GROUPMEMBERSHIPISHISTORICAL, RECOGNITIONID, REVENUEID, REVENUESPLITID,

                    --        REVENUEDATE, REVENUEDATEADDED, REVENUECONSTITUENTID, RECOGNITIONDATE, REVENUERECOGNITIONTYPECODEID, RECOGNITIONAMOUNT, RECOGNITIONDATEADDED                                                        

                    --from dbo.UFN_GROUP_RECOGNITIONCREDITS(null, @REVENUETYPECODES, null, null, @RECOGNITIONTYPES, 1, @ASOF, 1, null, @SELECTIONS, @REVENUEAPPLICATIONSELECTION);


          create index IX_TMP_GROUP_RECOGNITIONCREDITS_GROUPID on #TMP_GROUP_RECOGNITIONCREDITS (GROUPID)
          create index IX_TMP_GROUP_RECOGNITIONCREDITS_GROUPMEMBERID on #TMP_GROUP_RECOGNITIONCREDITS (GROUPMEMBERID)

                --First gather the data for members

                insert into #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS(RECORDID, MEMBERID, HOUSEHOLDID, GROUPINGID, ISHISTORICAL, AMOUNT, DATE, DATEADDED)
                    select distinct
                        [DATA].REVENUESPLITID,
                        [MEMBERS].MEMBERID,
                        [DATA].GROUPID,
                        [MEMBERS].MEMBERID,
                        [DATA].GROUPMEMBERSHIPISHISTORICAL,
                        [DATA].RECOGNITIONAMOUNT,
                        [DATA].RECOGNITIONDATE,
                        [DATA].RECOGNITIONDATEADDED
                    from #TMP_MEMBERS_HH_REVENUESPLITANDRECOGNITIONS as [MEMBERS]
                    left join #TMP_GROUP_RECOGNITIONCREDITS as [DATA] on [DATA].GROUPID = [MEMBERS].HOUSEHOLDID
                    where (@COMPUTEDSTARTDATE is null or [DATA].RECOGNITIONDATE >= @COMPUTEDSTARTDATE)
                        and (@COMPUTEDENDDATE is null or [DATA].RECOGNITIONDATE <= @COMPUTEDENDDATE)
                        and ((@INCLUDEHISTORICALDATA = 0 and [DATA].GROUPMEMBERSHIPISHISTORICAL = 0) or (@INCLUDEHISTORICALDATA = 1));

            --Second gather the data for households and individuals not in a household                

      ;with CTE_DATA as
        (select 
              GROUPID,
              GROUPMEMBERID,
              REVENUESPLITID,
              RECOGNITIONAMOUNT,
                          RECOGNITIONDATE,
                          RECOGNITIONDATEADDED
        from #TMP_GROUP_RECOGNITIONCREDITS
        where (@COMPUTEDSTARTDATE is null or REVENUEDATE >= @COMPUTEDSTARTDATE)  
              and (@COMPUTEDENDDATE is null or REVENUEDATE <= @COMPUTEDENDDATE)  
              and ((@INCLUDEHISTORICALDATA = 0 and GROUPMEMBERSHIPISHISTORICAL = 0) or (@INCLUDEHISTORICALDATA = 1) or GROUPMEMBERSHIPISHISTORICAL is null)  
        )
        insert into #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS(RECORDID, MEMBERID, HOUSEHOLDID, GROUPINGID, ISHISTORICAL, AMOUNT, DATE, DATEADDED)  
             select distinct 
              coalesce([DATA_GROUP].REVENUESPLITID, [DATA_MEMBER].REVENUESPLITID),
              CONSTITUENT.ID,  
              null,  
              CONSTITUENT.ID,  
              0,  
              coalesce([DATA_GROUP].RECOGNITIONAMOUNT, [DATA_MEMBER].RECOGNITIONAMOUNT),
              coalesce([DATA_GROUP].RECOGNITIONDATE, [DATA_MEMBER].RECOGNITIONDATE),
              coalesce([DATA_GROUP].RECOGNITIONDATEADDED, [DATA_MEMBER].RECOGNITIONDATEADDED)
             from dbo.CONSTITUENT  
             left join CTE_DATA as [DATA_GROUP] on [DATA_GROUP].GROUPID = CONSTITUENT.ID
             left join CTE_DATA as [DATA_MEMBER] on [DATA_MEMBER].GROUPMEMBERID = CONSTITUENT.ID
             where 
             CONSTITUENT.ID not in (select GROUPMEMBER.MEMBERID from dbo.GROUPMEMBER
                                    inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
                                    inner join dbo.CONSTITUENTHOUSEHOLD CHH on CHH.ID = GROUPMEMBER.MEMBERID
                                    where GROUPDATA.GROUPTYPECODE = 0 and HOUSEHOLDID is not NULL)

                --Third, gather the data for constituents who do not have any gifts in the @CALCULATEDSTARTDATE/ENDDATE range. This will put $0 on their smart field value

                --This fixes WI60704

                insert into #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS(RECORDID, MEMBERID, HOUSEHOLDID, GROUPINGID, ISHISTORICAL, AMOUNT, DATE, DATEADDED)
                    select
                        null,
                        CONSTITUENT.ID,
                        null,
                        CONSTITUENT.ID,
                        0,
                        0,
                        null,
                        null
                    from dbo.CONSTITUENT 
                    inner join #TMP_GROUP_RECOGNITIONCREDITS as [RECOGNITIONFUNCTIONDATA] on [RECOGNITIONFUNCTIONDATA].GROUPMEMBERID = CONSTITUENT.ID
                    left join #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA] on [DATA].GROUPINGID = CONSTITUENT.ID
                    where [DATA].GROUPINGID is null

                --Fourth, gather data for members who do not have any gifts yet. This will put $o on their smart field value 

                --This fixes WI75799                

                insert into #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS(RECORDID, MEMBERID, HOUSEHOLDID, GROUPINGID, ISHISTORICAL, AMOUNT, DATE, DATEADDED)
                    select
                        null,
                        [MEMBERS].MEMBERID,
                        null,
                        [MEMBERS].MEMBERID,
                        0,
                        0,
                        null,
                        null
                    from #TMP_MEMBERS_HH_REVENUESPLITANDRECOGNITIONS as [MEMBERS]
                    left join #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA] on [DATA].GROUPINGID = [MEMBERS].MEMBERID
                    where [DATA].GROUPINGID is null

            end

            declare @revenuesql nvarchar(max)

            -- Backwards compatibility

            if @SMARTFIELDID is not null
                set @revenuesql = 'with SF_CTE (ID, VALUE) as ('
            else
                set @revenuesql = ''

            /* Calculate totals based on calculation type. */
            if @CALCULATIONTYPE = 0 --Max(amount)

                set @revenuesql = @revenuesql + char(10) + '
                    select
                        [DATA].GROUPINGID,
                        coalesce(max([DATA].AMOUNT), 0)
                    from ( 
                            select distinct
                                [DATA].GROUPINGID,
                                [DATA].RECORDID,
                                sum([DATA].AMOUNT) AMOUNT
                            from 
                                #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                            group by [DATA].GROUPINGID, [DATA].RECORDID, [DATA].ISHISTORICAL
                        ) as [DATA]
                    group by [DATA].GROUPINGID'

            else if @CALCULATIONTYPE = 1 --Min(amount)

                set @revenuesql = @revenuesql + char(10) + '
                    select
                        [DATA].GROUPINGID,
                        coalesce(min([DATA].AMOUNT), 0)
                    from ( 
                            select distinct
                                [DATA].GROUPINGID,
                                [DATA].RECORDID,
                                sum([DATA].AMOUNT) AMOUNT
                            from 
                                #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                            group by [DATA].GROUPINGID, [DATA].RECORDID, [DATA].ISHISTORICAL
                        ) as [DATA]
                    group by [DATA].GROUPINGID'

            else if @CALCULATIONTYPE = 2 --Sum(amount)

                set @revenuesql = @revenuesql + char(10) + '
                    select
                        [DATA].GROUPINGID,
                        coalesce(sum([DATA].AMOUNT), 0)
                    from ( 
                            select distinct
                                [DATA].GROUPINGID,
                                [DATA].RECORDID,
                                sum([DATA].AMOUNT) AMOUNT
                            from 
                                #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                            group by [DATA].GROUPINGID, [DATA].RECORDID, [DATA].ISHISTORICAL
                        ) as [DATA]
                    group by [DATA].GROUPINGID'

            else if @CALCULATIONTYPE = 3 --Avg(amount) 

                set @revenuesql = @revenuesql + char(10) + '
                    select
                        [DATA].GROUPINGID,
                        coalesce(avg([DATA].AMOUNT), 0)
                    from ( 
                            select distinct
                                [DATA].GROUPINGID,
                                [DATA].RECORDID,
                                sum([DATA].AMOUNT) AMOUNT
                            from 
                                #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                            group by [DATA].GROUPINGID, [DATA].RECORDID, [DATA].ISHISTORICAL
                        ) as [DATA]
                    group by [DATA].GROUPINGID'

            else if @CALCULATIONTYPE = 4 --First revenue amount

                set @revenuesql = @revenuesql + char(10) + '
                  select
                    [DATA].GROUPINGID,
                    coalesce(sum([DATA].AMOUNT), 0)
                  from
                  (
                      select distinct
                          [DATA].GROUPINGID,
                          [DATA].RECORDID,
                          coalesce(sum([DATA].AMOUNT), 0) as AMOUNT
                      from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                      where [DATA].RECORDID = (select top 1 RECORDID from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [SUBD] where [SUBD].GROUPINGID = [DATA].GROUPINGID order by [SUBD].DATE asc, [SUBD].DATEADDED asc)
                      group by [DATA].GROUPINGID, [DATA].RECORDID, [DATA].ISHISTORICAL
                  ) as [DATA]
                  group by [DATA].GROUPINGID
                  union  /*JamesWill 2009-10-14 WI60984, make sure not to add duplicate data for constits with revenue records. (the union does distinct by default)*/
                  select distinct
                      [DATA].GROUPINGID,
                      0
                  from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                  left join #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA2] on [DATA2].GROUPINGID = [DATA].GROUPINGID
                  where [DATA].RECORDID is null and not exists(select top 1 RECORDID from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS where GROUPINGID = [DATA].GROUPINGID and RECORDID is not null)
             group by [DATA].GROUPINGID'

            else if @CALCULATIONTYPE = 5 --Latest revenue amount

                set @revenuesql = @revenuesql + char(10) + '
                    select
                      [DATA].GROUPINGID,
                      coalesce(sum([DATA].AMOUNT), 0)
                    from
                    (
                      select distinct
                          [DATA].GROUPINGID,
                          [DATA].RECORDID,
                          coalesce(sum([DATA].AMOUNT), 0) as AMOUNT
                      from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                      where [DATA].RECORDID = (select top 1 RECORDID from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [SUBD] where [SUBD].GROUPINGID = [DATA].GROUPINGID order by [SUBD].DATE desc, [SUBD].DATEADDED desc)
                      group by [DATA].GROUPINGID, [DATA].RECORDID, [DATA].ISHISTORICAL
                    ) as [DATA]
                    group by [DATA].GROUPINGID
                    union --JamesWill 2009-10-14 WI60984, make sure not to add duplicate data for constits with revenue records. (the union does distinct by default)

                    select distinct
                        [DATA].GROUPINGID, 
                        0
                    from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                    left join #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA2] on [DATA2].GROUPINGID = [DATA].GROUPINGID
                    where [DATA].RECORDID is null and not exists(select top 1 RECORDID from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS where GROUPINGID = [DATA].GROUPINGID and RECORDID is not null)
                    group by [DATA].GROUPINGID'

            else if @CALCULATIONTYPE = 6 --Min(date)

                set @revenuesql = @revenuesql + char(10) + '
                    select
                        [DATA].GROUPINGID,
                        min([DATA].DATE)
                    from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                    group by [DATA].GROUPINGID'

            else if @CALCULATIONTYPE = 7 --Max(date)

                set @revenuesql = @revenuesql + char(10) + '
                    select
                        [DATA].GROUPINGID,
                        max([DATA].DATE)
                    from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                    group by [DATA].GROUPINGID'

            else if @CALCULATIONTYPE = 8 --Giving years

                set @revenuesql = @revenuesql + char(10) + '
                    select
                        [DATA].GROUPINGID,
                        coalesce(datediff(year,min([DATA].DATE),max([DATA].DATE)), -1) + 1
                    from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                    group by [DATA].GROUPINGID'

            else if @CALCULATIONTYPE = 9 --Distinct giving years

                set @revenuesql = @revenuesql + char(10) + '
                    select
                        [DATA].GROUPINGID,
                        count(distinct [YEARS].YEAR)
                    from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                    left join (select distinct [SUBD].GROUPINGID, datepart(year,[SUBD].DATE) as [YEAR] from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [SUBD]) as [YEARS] on [YEARS].GROUPINGID = [DATA].GROUPINGID
                    group by [DATA].GROUPINGID'

            else if @CALCULATIONTYPE = 10 --Count(records)

                set @revenuesql = @revenuesql + char(10) + '
                    select
                        [DATA].GROUPINGID,
                        count([DATA].RECORDID)
                    from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                    group by [DATA].GROUPINGID'

            else if @CALCULATIONTYPE = 11 --Consecutive giving years

            begin
                set @revenuesql = '
                    with MAXREVENUEDATE([GROUPINGID], [DATE]) as
                    (
                        select [DATA].GROUPINGID, max([DATA].DATE)
                        from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                        group by [DATA].GROUPINGID
                        having max([DATA].DATE) > getdate()
                    ),
                    [BANDING]([GROUPINGID], [BAND]) as
                    (
                        select distinct
                            [DATA].GROUPINGID,
                            0
                        from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]

                        union all
                        select
                            [DATA].GROUPINGID,
                            (datediff(month, [DATA].[DATE], coalesce([MAXREVENUEDATE].[DATE], getdate()))/12) + 1  
                        from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                        left join [MAXREVENUEDATE] on [MAXREVENUEDATE].GROUPINGID = [DATA].GROUPINGID
                    ),
                    [GAPS]([GROUPINGID], [BAND]) as
                    (
                        select distinct
                            [BANDING].GROUPINGID,
                            [BANDING].BAND
                        from [BANDING]
                        where not exists (select 1 from [BANDING] as [SUBB] where [SUBB].GROUPINGID = [BANDING].GROUPINGID and [SUBB].BAND = [BANDING].BAND + 1)
                    )'

                if @SMARTFIELDID is not null
                    set @revenuesql = @revenuesql + ', SF_CTE (ID, VALUE) as ('

                set @revenuesql = @revenuesql + char(10) +
                    'select distinct
                        [GAPS].GROUPINGID,
                        min([GAPS].BAND)
                    from [GAPS]
                    group by [GAPS].[GROUPINGID]'
            end
            else if @CALCULATIONTYPE = 12 --First date of largest amount

                set @revenuesql = @revenuesql + char(10) + '
                    select distinct
                        [GROUPINGIDS].GROUPINGID,
                        [DATA].DATE
                    from 
                        (select distinct GROUPINGID from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS) [GROUPINGIDS]
                        outer apply (select top 1 [SUBD].DATE from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [SUBD] where [SUBD].GROUPINGID = [GROUPINGIDS].GROUPINGID order by [SUBD].AMOUNT desc, [SUBD].DATE) DATA'

            else if @CALCULATIONTYPE = 13 --Is annual giver

                set @revenuesql = @revenuesql + char(10) + '
                    select
                        [DATA].GROUPINGID,
                        case
                            when (coalesce(datediff(year, min([DATA].DATE), max([DATA].DATE)), 0) = ([DATES].YEARS - 1)) and ([DATES].YEARS > 1)
                                then cast(1 as bit)
                            else
                                cast(0 as bit)
                        end
                    from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                    left join (select [SUBD].GROUPINGID, count(distinct datepart(year, [SUBD].DATE)) as [YEARS] from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [SUBD] group by [SUBD].GROUPINGID) as [DATES] on [DATES].GROUPINGID = [DATA].GROUPINGID
                    group by [DATA].GROUPINGID, [DATES].YEARS'

            else
                raiserror('ERR_USP_CONSTITUENTGROUPSMARTFIELD_REVENUEANDRECOGNITION_AGGREGATE_INVALID_CALCTYPE', 13, 1);

            -- Backwards compatibility

            if @SMARTFIELDID is null
            begin
                insert into #TMP_CONSTITUENTSMARTFIELD_HH_REVENUESPLITANDRECOGNITIONS
                exec sp_executesql @revenuesql;
            end
            else
            begin
                declare @TARGET nvarchar(100)
                declare @TARGET_FIELD nvarchar(128)
                select
                    @TARGET = TABLECATALOG.TABLENAME,
                    @TARGET_FIELD = SMARTFIELD.VALUECOLUMNNAME
                from dbo.SMARTFIELD
                    left join dbo.TABLECATALOG on SMARTFIELD.TABLECATALOGID = TABLECATALOG.ID
                where SMARTFIELD.ID = @SMARTFIELDID

                set @revenuesql = @revenuesql + ')
                    merge dbo.' + @TARGET + ' as target
                    using SF_CTE as source
                    on target.ID = source.ID
                    when matched then
                        update set ' + @TARGET_FIELD + ' = source.VALUE
                    when not matched by target then
                        insert (ID, ' + @TARGET_FIELD + ')
                        values (source.ID, source.VALUE);';

                 exec sp_executesql @revenuesql;

                 select @@ROWCOUNT;
            end