USP_CONSTITUENTSOURCEANALYSIS_UPDATECOMMUNITYMEMBER

            CREATE procedure dbo.USP_CONSTITUENTSOURCEANALYSIS_UPDATECOMMUNITYMEMBER
            (
                @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.CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER
                    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],
                        CONSTITUENT.DATECHANGED,
                        CONSTITUENT.NETCOMMUNITYMEMBER
                    from dbo.CONSTITUENT
                    inner join dbo.CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER on CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER.CONSTITUENTID = CONSTITUENT.ID
                    where (CONSTITUENT.DATECHANGED > CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER.DATEFROM)
                ) 
                update CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER
                        set CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER.NEEDSUPDATE = 1,
                            CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER.DATETO = [CONSTIT_INFO].DATECHANGED,
                            CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER.CHANGEDBYID = @CHANGEAGENTID,
                            CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER.DATECHANGED = @CHANGEDATE
                    from CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER
                    inner join [CONSTIT_INFO] on [CONSTIT_INFO].CONSTITUENTID = CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER.CONSTITUENTID and CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER.DATETO is null
                    where
                        [CONSTIT_INFO].NETCOMMUNITYMEMBER <> CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER.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],
                        CONSTITUENT.DATECHANGED,
                        CONSTITUENT.NETCOMMUNITYMEMBER
                    from dbo.CONSTITUENT
                    left join dbo.CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER on CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER.CONSTITUENTID = CONSTITUENT.ID
                    where CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER.ID is null or CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER.NEEDSUPDATE = 1
                )
                insert into dbo.CONSTITUENTSOURCEANALYSISCACHECOMMUNITYMEMBER(ID, NEEDSUPDATE, CONSTITUENTID, DATEFROM, DATETO, VALUE,
                                                                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        newid(),
                        0,
                        [CONSTIT_INFO].CONSTITUENTID,
                        [CONSTIT_INFO].DATECHANGED,
                        null,
                        [CONSTIT_INFO].NETCOMMUNITYMEMBER,

                        @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;