USP_CONSTITUENTSOURCEANALYSIS_UPDATEMGO

            create procedure dbo.USP_CONSTITUENTSOURCEANALYSIS_UPDATEMGO
            (
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
            set nocount on;

            begin try
                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
                if @CHANGEDATE is null
                    set @CHANGEDATE = getdate();

                --reset the NEEDSUPDATE flag from previous runs

                update dbo.CONSTITUENTSOURCEANALYSISCACHEMGORG
                    set NEEDSUPDATE = 0,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CHANGEDATE
                where NEEDSUPDATE = 1;    

                --any records that have changed since last time needs to have their DATETO date set. Also set NEEDSUPDATE so the 

                --insert statement will know to write the updated data to the cache

                with [CONSTIT_INFO] as
                (
                    select
                        CONSTITUENT.ID as [CONSTITUENTID],
                        coalesce(max(MATCHINGGIFTCONDITION.DATECHANGED), @CHANGEDATE) as [DATECHANGED],
                        case when MATCHINGGIFTCONDITION.ORGANIZATIONID is null then 0 else 1 end as [MATCHESGIFTS]
                    from dbo.CONSTITUENT
                    left join dbo.MATCHINGGIFTCONDITION on CONSTITUENT.ID = MATCHINGGIFTCONDITION.ORGANIZATIONID 
                    inner join dbo.CONSTITUENTSOURCEANALYSISCACHEMGORG on CONSTITUENTSOURCEANALYSISCACHEMGORG.CONSTITUENTID = CONSTITUENT.ID
                    where (@CHANGEDATE > CONSTITUENTSOURCEANALYSISCACHEMGORG.DATEFROM)
                    group by CONSTITUENT.ID, MATCHINGGIFTCONDITION.ORGANIZATIONID
                ) 
                update CONSTITUENTSOURCEANALYSISCACHEMGORG
                        set CONSTITUENTSOURCEANALYSISCACHEMGORG.NEEDSUPDATE = 1,
                            CONSTITUENTSOURCEANALYSISCACHEMGORG.DATETO = @CHANGEDATE,
                            CONSTITUENTSOURCEANALYSISCACHEMGORG.CHANGEDBYID = @CHANGEAGENTID,
                            CONSTITUENTSOURCEANALYSISCACHEMGORG.DATECHANGED = @CHANGEDATE
                    from CONSTITUENTSOURCEANALYSISCACHEMGORG
                    inner join [CONSTIT_INFO] on [CONSTIT_INFO].CONSTITUENTID = CONSTITUENTSOURCEANALYSISCACHEMGORG.CONSTITUENTID and CONSTITUENTSOURCEANALYSISCACHEMGORG.DATETO is null
                    where
                        [CONSTIT_INFO].MATCHESGIFTS <> CONSTITUENTSOURCEANALYSISCACHEMGORG.VALUE;


                --For any constituents that either don't have a record in the cache table OR have a NEEDSUPDATE flag set, insert their

                --current address data into the cache

                with [CONSTIT_INFO] as 
                (
                    select
                        CONSTITUENT.ID as [CONSTITUENTID],
                        coalesce(max(MATCHINGGIFTCONDITION.DATECHANGED), @CHANGEDATE) as [DATECHANGED],
                        case when MATCHINGGIFTCONDITION.ORGANIZATIONID is null then 0 else 1 end as [MATCHESGIFTS]
                    from dbo.CONSTITUENT
                    left join dbo.MATCHINGGIFTCONDITION on CONSTITUENT.ID = MATCHINGGIFTCONDITION.ORGANIZATIONID 
                    left join dbo.CONSTITUENTSOURCEANALYSISCACHEMGORG on CONSTITUENTSOURCEANALYSISCACHEMGORG.CONSTITUENTID = CONSTITUENT.ID
                    where CONSTITUENTSOURCEANALYSISCACHEMGORG.ID is null or CONSTITUENTSOURCEANALYSISCACHEMGORG.NEEDSUPDATE = 1
                    group by CONSTITUENT.ID, MATCHINGGIFTCONDITION.ORGANIZATIONID
                )
                insert into dbo.CONSTITUENTSOURCEANALYSISCACHEMGORG(ID, NEEDSUPDATE, CONSTITUENTID, DATEFROM, DATETO, VALUE,
                                                                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        newid(),
                        0,
                        [CONSTIT_INFO].CONSTITUENTID,
                        @CHANGEDATE,
                        null,
                        [CONSTIT_INFO].MATCHESGIFTS,

                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE
                    from [CONSTIT_INFO];

                --No need to reset the NEEDSUPDATE flag since that will be handled the next time this procedure is run

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

            return 0;