USP_CONSTITUENTIDSMARTFIELD_HH_REVENUESPLITANDRECOGNITIONS

            CREATE procedure dbo.USP_CONSTITUENTIDSMARTFIELD_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;
begin try            
            --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

            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,
                                TSLONG bigint
            )

            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, TSLONG)
                    select 
                        [DATA].REVENUESPLITID,
                        [MEMBERS].MEMBERID,
                        [DATA].GROUPID,
                        [MEMBERS].MEMBERID,
                        [DATA].GROUPMEMBERSHIPISHISTORICAL,
                        [DATA].REVENUESPLITAMOUNT,
                        [DATA].REVENUEDATE,
                        [DATA].REVENUEDATEADDED,
                                                [DATA].TSLONG
                    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,
                        TSLONG
                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, TSLONG)
                         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),
                            coalesce([DATA_GROUP].TSLONG, [DATA_MEMBER].TSLONG)
                         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 where GROUPDATA.GROUPTYPECODE = 0)            

                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, TSLONG)
                    select
                        null,
                        CONSTITUENT.ID,
                        null,
                        CONSTITUENT.ID,
                        0,
                        0,
                        null,
                        null,
                                                0
                    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, TSLONG)
                    select
                        null,
                        [MEMBERS].MEMBERID,
                        null,
                        [MEMBERS].MEMBERID,
                        0,
                        0,
                        null,
                        null,
                                                0
                    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)


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

                --First gather the data for members

                insert into #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS(RECORDID, MEMBERID, HOUSEHOLDID, GROUPINGID, ISHISTORICAL, AMOUNT, DATE, DATEADDED, TSLONG)
                    select
                        [DATA].REVENUESPLITID,
                        [MEMBERS].MEMBERID,
                        [DATA].GROUPID,
                        [MEMBERS].MEMBERID,
                        [DATA].GROUPMEMBERSHIPISHISTORICAL,
                        [DATA].RECOGNITIONAMOUNT,
                        [DATA].RECOGNITIONDATE,
                        [DATA].RECOGNITIONDATEADDED,
                        [DATA].TSLONG
                    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 is null or [DATA].RECOGNITIONDATE >= @COMPUTEDSTARTDATE)
                        and (@COMPUTEDENDDATE is null or [DATA].RECOGNITIONDATE 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,
                                                    TSLONG
                from #TMP_GROUP_RECOGNITIONCREDITS
                where (@COMPUTEDSTARTDATE is null or RECOGNITIONDATE >= @COMPUTEDSTARTDATE)  
                            and (@COMPUTEDENDDATE is null or RECOGNITIONDATE <= @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, TSLONG)  
                         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),
                            coalesce([DATA_GROUP].TSLONG, [DATA_MEMBER].TSLONG)
                         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 where GROUPDATA.GROUPTYPECODE = 0);

            --Find out top 1 revenue record which needs to be updated based on the calculation type

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

            create table #TMP_GROUP_RECOGNITIONCREDITS_FORUPDATE
            (
                REVENUESPLITID uniqueidentifier,
                GROUPMEMBERID uniqueidentifier,
                RECOGNITIONAMOUNT money,
                RECOGNITIONDATE datetime,
                RECOGNITIONDATEADDED datetime,
                TSLONG bigint
            );

            declare @SQL_UPDATERECOGNITIONS nvarchar(max) = null;
            declare @SQL_ORDERBY nvarchar(max) = null;

            if @CALCULATIONTYPE = 0 --Max(amount)

                set @SQL_ORDERBY = 'row_number() over(partition by [DATA].[GROUPMEMBERID] 
                        order by [DATA].[RECOGNITIONAMOUNT] desc, [DATA].[RECOGNITIONDATE] desc, [DATA].[RECOGNITIONDATEADDED] desc, [DATA].[TSLONG] desc, [DATA].[REVENUESPLITID]) [RANK]'

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

                set @SQL_ORDERBY = 'row_number() over(partition by [DATA].[GROUPMEMBERID] 
                        order by [DATA].[RECOGNITIONAMOUNT] asc, [DATA].[RECOGNITIONDATE] desc, [DATA].[RECOGNITIONDATEADDED] desc, [DATA].[TSLONG] desc, [DATA].[REVENUESPLITID]) [RANK]'

            else if @CALCULATIONTYPE = 4 --First revenue amount

                set @SQL_ORDERBY = ' row_number() over(partition by [DATA].[GROUPMEMBERID] order by [DATA].[RECOGNITIONDATE] asc, [DATA].[RECOGNITIONDATEADDED] asc, [DATA].[TSLONG] asc, [DATA].[REVENUESPLITID]) [RANK]'

            else if @CALCULATIONTYPE = 5 --Latest revenue amount

                set @SQL_ORDERBY = ' row_number() over(partition by [DATA].[GROUPMEMBERID] order by [DATA].[RECOGNITIONDATE] desc, [DATA].[RECOGNITIONDATEADDED] desc, [DATA].[TSLONG] desc, [DATA].[REVENUESPLITID]) [RANK]';

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

            set @SQL_UPDATERECOGNITIONS = '
                ;with [REVENUERANKS] ([REVENUESPLITID], [RANK]) as
                    (
                        select 
                            [DATA].[REVENUESPLITID] [REVENUESPLITID],
                            '+@SQL_ORDERBY+' 
              from #TMP_GROUP_RECOGNITIONCREDITS as [DATA]
              where (@COMPUTEDSTARTDATE is null or [DATA].[RECOGNITIONDATE] is null or [DATA].[RECOGNITIONDATE] >= @COMPUTEDSTARTDATE)  
                        and (@COMPUTEDENDDATE is null or [DATA].[RECOGNITIONDATE] is null or [DATA].[RECOGNITIONDATE] <= @COMPUTEDENDDATE)
                    )

                    insert into #TMP_GROUP_RECOGNITIONCREDITS_FORUPDATE
                    select 
                        [DATA].REVENUESPLITID,
                        [DATA].[GROUPMEMBERID],
                        [DATA].[RECOGNITIONAMOUNT],
                        [DATA].[RECOGNITIONDATE],
                        [DATA].[RECOGNITIONDATEADDED],
                        [DATA].[TSLONG]
                    from #TMP_GROUP_RECOGNITIONCREDITS [DATA]
                    inner join [REVENUERANKS] [REVENUERANKS] on [DATA].[REVENUESPLITID] = [REVENUERANKS].[REVENUESPLITID]
                    where [REVENUERANKS].[RANK] = 1;
             ';
            exec sp_executesql @SQL_UPDATERECOGNITIONS, N'@COMPUTEDSTARTDATE datetime, @COMPUTEDENDDATE datetime', @COMPUTEDSTARTDATE, @COMPUTEDENDDATE;

            --Update data for the members of a dissolved household

            update DATA
                set RECORDID = [GROUP_DATA].REVENUESPLITID,
                    AMOUNT = [GROUP_DATA].RECOGNITIONAMOUNT,
                    DATE = [GROUP_DATA].RECOGNITIONDATE,
                    DATEADDED = [GROUP_DATA].RECOGNITIONDATEADDED,
                    TSLONG = [GROUP_DATA].TSLONG
            from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS [DATA]
                inner join #TMP_GROUP_RECOGNITIONCREDITS_FORUPDATE as [GROUP_DATA] on [GROUP_DATA].GROUPMEMBERID = [DATA].MEMBERID
            where [DATA].MEMBERID in (select MEMBERID from dbo.GROUPMEMBER where GROUPID = [DATA].HOUSEHOLDID)
                and [DATA].RECORDID is 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, TSLONG)
                    select
                        null,
                        CONSTITUENT.ID,
                        null,
                        CONSTITUENT.ID,
                        0,
                        0,
                        null,
                        null,
                                                0
                    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, TSLONG)
                    select
                        null,
                        [MEMBERS].MEMBERID,
                        null,
                        [MEMBERS].MEMBERID,
                        0,
                        0,
                        null,
                        null,
                                                0
                    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 @sql nvarchar(max)
                        declare @exec_logic nvarchar(max)

                        -- Backwards compatibility

                        if @SMARTFIELDID is null
                        begin
                                set @exec_logic = 'insert into #TMP_CONSTITUENTSMARTFIELD_HH_REVENUESPLITANDRECOGNITIONS (ID, VALUE)'
                        end
                        else
                        begin
                                set @exec_logic = ', SF_CTE (ID, VALUE) as ('
                        end

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

                                set @sql = '
                                        with [REVENUERANKS] ([CONSTITUENTID], [REVENUEID], [RANK]) as
                                        (
                                select 
                                    [DATA].[MEMBERID] [CONSTITUENTID],
                                    [DATA].[RECORDID] [REVENUEID],
                                                        row_number() over(partition by [DATA].[GROUPINGID] order by [DATA].[AMOUNT] desc, [DATA].[DATE] desc, [DATA].[DATEADDED] desc, [DATA].[TSLONG] desc, [DATA].[RECORDID]) [RANK]
                                from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                                        )
                                        ' + @exec_logic + '
                            select
                                    [REVENUERANKS].[CONSTITUENTID],
                                    [REVENUERANKS].[REVENUEID]
                                from [REVENUERANKS]
                                        where isnull([RANK], 1) = 1';

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

                                set @sql = '
                        with [REVENUERANKS] ([CONSTITUENTID], [REVENUEID], [RANK]) as
                                        (
                                select 
                                    [DATA].[MEMBERID],
                                    [DATA].[RECORDID],
                                                        row_number() over(partition by [DATA].[GROUPINGID] order by [DATA].[AMOUNT] asc, [DATA].[DATE] desc, [DATA].[DATEADDED] desc, [DATA].[TSLONG] desc, [DATA].[RECORDID]) [RANK]
                                from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                                        )
                                        ' + @exec_logic + '
                            select 
                                    [REVENUERANKS].[CONSTITUENTID],
                                    [REVENUERANKS].[REVENUEID]
                                from [REVENUERANKS] as [REVENUERANKS]
                                        where isnull([REVENUERANKS].[RANK], 1) = 1';

            else if @CALCULATIONTYPE = 4 --First revenue amount

                                set @sql = '
                        with [REVENUERANKS] ([CONSTITUENTID], [REVENUEID], [RANK]) as
                                        (
                                select 
                                    [DATA].[MEMBERID],
                                    [DATA].[RECORDID],
                                                        row_number() over(partition by [DATA].[GROUPINGID] order by [DATA].[DATE] asc, [DATA].[DATEADDED] asc, [DATA].[TSLONG] asc, [DATA].[RECORDID]) [RANK]
                                from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                                        )
                                        ' + @exec_logic + '
                            select 
                                    [REVENUERANKS].[CONSTITUENTID],
                                    [REVENUERANKS].[REVENUEID]
                                from [REVENUERANKS] as [REVENUERANKS]
                                        where isnull([REVENUERANKS].[RANK], 1) = 1
                        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],
                            NULL
                        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)';

            else if @CALCULATIONTYPE = 5 --Latest revenue amount

                                set @sql = '
                        with [REVENUERANKS] ([CONSTITUENTID], [REVENUEID], [RANK]) as
                                        (
                                select 
                                    [DATA].[MEMBERID],
                                    [DATA].[RECORDID],
                                                        row_number() over(partition by [DATA].[GROUPINGID] order by [DATA].[DATE] desc, [DATA].[DATEADDED] desc, [DATA].[TSLONG] desc, [DATA].[RECORDID]) [RANK]
                                from #TMP_DATA_HH_REVENUESPLITANDRECOGNITIONS as [DATA]
                                        )
                                        ' + @exec_logic + '
                            select 
                                    [REVENUERANKS].[CONSTITUENTID],
                                    [REVENUERANKS].[REVENUEID]
                                from [REVENUERANKS] as [REVENUERANKS]
                                        where isnull([REVENUERANKS].[RANK], 1) = 1
                        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, 
                            NULL
                        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)';

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

                        -- Backwards compatibility

                        if @SMARTFIELDID is null
                        begin
                                exec sp_executesql @sql;
                        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 @sql = @sql + ')
                                        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 @sql;

                                 select @@ROWCOUNT;
                        end
end try

begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
end catch