USP_CONSTITUENTSOURCEANALYSIS_UPDATEADDRESS

            CREATE procedure dbo.USP_CONSTITUENTSOURCEANALYSIS_UPDATEADDRESS
            (
                @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.CONSTITUENTSOURCEANALYSISCACHEADDRESS
                    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],
                        case when ADDRESS.DATECHANGED is null then getdate() else ADDRESS.DATECHANGED end as [DATECHANGED],

                        ADDRESS.COUNTRYID,
                        ADDRESS.STATEID,
                        coalesce(ADDRESS.ADDRESSBLOCK, '') as [ADDRESSBLOCK],
                        coalesce(ADDRESS.CITY, '') as [CITY],
                        coalesce(ADDRESS.POSTCODE, '') as [POSTCODE]
                    from dbo.CONSTITUENT
                    left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                    inner join dbo.CONSTITUENTSOURCEANALYSISCACHEADDRESS on CONSTITUENTSOURCEANALYSISCACHEADDRESS.CONSTITUENTID = CONSTITUENT.ID
                    where (ADDRESS.DATECHANGED is null or ADDRESS.DATECHANGED > CONSTITUENTSOURCEANALYSISCACHEADDRESS.DATEFROM)
                ) 
                update CONSTITUENTSOURCEANALYSISCACHEADDRESS
                        set CONSTITUENTSOURCEANALYSISCACHEADDRESS.NEEDSUPDATE = 1,
                            CONSTITUENTSOURCEANALYSISCACHEADDRESS.DATETO = [CONSTIT_INFO].DATECHANGED,
                            CONSTITUENTSOURCEANALYSISCACHEADDRESS.CHANGEDBYID = @CHANGEAGENTID,
                            CONSTITUENTSOURCEANALYSISCACHEADDRESS.DATECHANGED = @CHANGEDATE
                    from CONSTITUENTSOURCEANALYSISCACHEADDRESS
                    inner join [CONSTIT_INFO] on [CONSTIT_INFO].CONSTITUENTID = CONSTITUENTSOURCEANALYSISCACHEADDRESS.CONSTITUENTID and CONSTITUENTSOURCEANALYSISCACHEADDRESS.DATETO is null
                    where
                           [CONSTIT_INFO].COUNTRYID <> CONSTITUENTSOURCEANALYSISCACHEADDRESS.PRIMARYCOUNTRYID
                        or [CONSTIT_INFO].STATEID <> CONSTITUENTSOURCEANALYSISCACHEADDRESS.PRIMARYSTATEID
                        or [CONSTIT_INFO].ADDRESSBLOCK <> CONSTITUENTSOURCEANALYSISCACHEADDRESS.PRIMARYADDRESSBLOCK
                        or [CONSTIT_INFO].CITY <> CONSTITUENTSOURCEANALYSISCACHEADDRESS.PRIMARYCITY
                        or [CONSTIT_INFO].POSTCODE <> CONSTITUENTSOURCEANALYSISCACHEADDRESS.PRIMARYPOSTCODE;


                --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],
                        case when ADDRESS.DATECHANGED is null then getdate() else ADDRESS.DATECHANGED end as [DATECHANGED],

                        ADDRESS.COUNTRYID,
                        ADDRESS.STATEID,
                        coalesce(ADDRESS.ADDRESSBLOCK, '') as [ADDRESSBLOCK],
                        coalesce(ADDRESS.CITY, '') as [CITY],
                        coalesce(ADDRESS.POSTCODE, '') as [POSTCODE]
                    from dbo.CONSTITUENT
                    left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                    left join dbo.CONSTITUENTSOURCEANALYSISCACHEADDRESS on CONSTITUENTSOURCEANALYSISCACHEADDRESS.CONSTITUENTID = CONSTITUENT.ID
                    where CONSTITUENTSOURCEANALYSISCACHEADDRESS.ID is null or CONSTITUENTSOURCEANALYSISCACHEADDRESS.NEEDSUPDATE = 1
                )
                insert into dbo.CONSTITUENTSOURCEANALYSISCACHEADDRESS(ID, NEEDSUPDATE, CONSTITUENTID, DATEFROM, DATETO,
                                                                PRIMARYCOUNTRYID, PRIMARYSTATEID, PRIMARYADDRESSBLOCk, PRIMARYCITY, PRIMARYPOSTCODE,
                                                                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        newid(),
                        0,
                        [CONSTIT_INFO].CONSTITUENTID,
                        [CONSTIT_INFO].DATECHANGED,
                        null,
                        [CONSTIT_INFO].COUNTRYID,
                        [CONSTIT_INFO].STATEID,
                        [CONSTIT_INFO].ADDRESSBLOCK,
                        [CONSTIT_INFO].CITY,
                        [CONSTIT_INFO].POSTCODE,

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