USP_CONSTITUENTSOURCEANALYSIS_UPDATEMEMBERSHIPMEMBER

            create procedure dbo.USP_CONSTITUENTSOURCEANALYSIS_UPDATEMEMBERSHIPMEMBER
            (
                @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.CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER
                    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(MEMBER.DATECHANGED), @CHANGEDATE) as [DATECHANGED],
                        case when MEMBER.CONSTITUENTID is null then 0 else 1 end as [ISMEMBER]
                    from dbo.CONSTITUENT
                    left join dbo.MEMBER on MEMBER.CONSTITUENTID = CONSTITUENT.ID and MEMBER.ISDROPPED = 0
                    left join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID and MEMBERSHIP.STATUSCODE <> 1
                    left join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = MEMBERSHIP.MEMBERSHIPLEVELTERMID and (MEMBERSHIPLEVELTERM.TERMCODE = 6 or getdate() <= dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALAFTEREXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID,MEMBERSHIP.EXPIRATIONDATE))
                    inner join dbo.CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER on CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER.CONSTITUENTID = CONSTITUENT.ID
                    where (@CHANGEDATE > CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER.DATEFROM)
                    group by MEMBER.CONSTITUENTID, CONSTITUENT.ID
                ) 
                update CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER
                        set CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER.NEEDSUPDATE = 1,
                            CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER.DATETO = @CHANGEDATE,
                            CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER.CHANGEDBYID = @CHANGEAGENTID,
                            CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER.DATECHANGED = @CHANGEDATE
                    from CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER
                    inner join [CONSTIT_INFO] on [CONSTIT_INFO].CONSTITUENTID = CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER.CONSTITUENTID and CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER.DATETO is null
                    where
                        [CONSTIT_INFO].ISMEMBER <> CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER.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(MEMBER.DATECHANGED), @CHANGEDATE) as [DATECHANGED],
                        case when MEMBER.CONSTITUENTID is null then 0 else 1 end as [ISMEMBER]
                    from dbo.CONSTITUENT
                    left join dbo.MEMBER on MEMBER.CONSTITUENTID = CONSTITUENT.ID and MEMBER.ISDROPPED = 0
                    left join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID and MEMBERSHIP.STATUSCODE <> 1
                    left join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = MEMBERSHIP.MEMBERSHIPLEVELTERMID and (MEMBERSHIPLEVELTERM.TERMCODE = 6 or getdate() <= dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALAFTEREXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID,MEMBERSHIP.EXPIRATIONDATE))
                    left join dbo.CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER on CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER.CONSTITUENTID = CONSTITUENT.ID
                    where CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER.ID is null or CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER.NEEDSUPDATE = 1
                    group by MEMBER.CONSTITUENTID, CONSTITUENT.ID
                )
                insert into dbo.CONSTITUENTSOURCEANALYSISCACHEMEMBERSHIPMEMBER(ID, NEEDSUPDATE, CONSTITUENTID, DATEFROM, DATETO, VALUE,
                                                                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        newid(),
                        0,
                        [CONSTIT_INFO].CONSTITUENTID,
                        @CHANGEDATE,
                        null,
                        [CONSTIT_INFO].ISMEMBER,

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