USP_MERGETWOCONSTITUENTS_BIOGRAPHICALDEMOGRAPHIC

            create procedure dbo.USP_MERGETWOCONSTITUENTS_BIOGRAPHICALDEMOGRAPHIC
            (
                @SOURCEID uniqueidentifier,
                @TARGETID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as
            begin
                set nocount on;

                declare @CURRENTDATE datetime = getdate();

                -- If Source and Target match on hash code and Source is Rejected/Confirmed, but Target is not Rejected/Confirmed, update the Target record values with Source record values. Notes will always be appended.

                update TARGETBIOGRAPHICALDEMOGRAPHIC
                set
                    [CONFIRMED] = SOURCEBIOGRAPHICALDEMOGRAPHIC.CONFIRMED
                    ,[DATECONFIRMED] = SOURCEBIOGRAPHICALDEMOGRAPHIC.DATECONFIRMED
                    ,[CONFIRMEDBYAPPUSERID] = SOURCEBIOGRAPHICALDEMOGRAPHIC.CONFIRMEDBYAPPUSERID
                    ,[REJECTED] = SOURCEBIOGRAPHICALDEMOGRAPHIC.REJECTED
                    ,[DATEREJECTED] = SOURCEBIOGRAPHICALDEMOGRAPHIC.DATEREJECTED
                    ,[REJECTEDBYAPPUSERID] = SOURCEBIOGRAPHICALDEMOGRAPHIC.REJECTEDBYAPPUSERID
                    ,[SOURCE] = SOURCEBIOGRAPHICALDEMOGRAPHIC.SOURCE
                    ,[FULLHASH] = SOURCEBIOGRAPHICALDEMOGRAPHIC.FULLHASH
                    ,[PARTIALHASH] = SOURCEBIOGRAPHICALDEMOGRAPHIC.PARTIALHASH
                    ,[MC] = SOURCEBIOGRAPHICALDEMOGRAPHIC.MC
                    ,[FULLNAME] = SOURCEBIOGRAPHICALDEMOGRAPHIC.FULLNAME
                    ,[FIRSTNAME] = SOURCEBIOGRAPHICALDEMOGRAPHIC.FIRSTNAME
                    ,[MIDDLENAME] = SOURCEBIOGRAPHICALDEMOGRAPHIC.MIDDLENAME
                    ,[LASTNAME] = SOURCEBIOGRAPHICALDEMOGRAPHIC.LASTNAME
                    ,[SUFFIXCODEID] = SOURCEBIOGRAPHICALDEMOGRAPHIC.SUFFIXCODEID
                    ,[VIEWED] = SOURCEBIOGRAPHICALDEMOGRAPHIC.VIEWED
                    ,[CNOTES] = case SOURCEBIOGRAPHICALDEMOGRAPHIC.CNOTES when '' then TARGETBIOGRAPHICALDEMOGRAPHIC.CNOTES else TARGETBIOGRAPHICALDEMOGRAPHIC.CNOTES + (case when TARGETBIOGRAPHICALDEMOGRAPHIC.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCEBIOGRAPHICALDEMOGRAPHIC.CNOTES end
                    ,[BIRTHDATE] = SOURCEBIOGRAPHICALDEMOGRAPHIC.BIRTHDATE
                    ,[OCCUPATION] = SOURCEBIOGRAPHICALDEMOGRAPHIC.OCCUPATION
                    ,[MARITALSTATUSCODEID] = SOURCEBIOGRAPHICALDEMOGRAPHIC.MARITALSTATUSCODEID
                    ,[HOUSEHOLD_MOSAIC] = SOURCEBIOGRAPHICALDEMOGRAPHIC.HOUSEHOLD_MOSAIC
                    ,[DISCRETIONARY_SPEND] = SOURCEBIOGRAPHICALDEMOGRAPHIC.DISCRETIONARY_SPEND
                    ,[CHANGEDBYID] = @CHANGEAGENTID
                    ,[DATECHANGED] = @CURRENTDATE
                    ,[HOUSEHOLD_INCOMECODEID] = SOURCEBIOGRAPHICALDEMOGRAPHIC.HOUSEHOLD_INCOMECODEID
                    ,[TITLECODEID] = SOURCEBIOGRAPHICALDEMOGRAPHIC.TITLECODEID
                from dbo.WPBIOGRAPHICALDEMOGRAPHIC TARGETBIOGRAPHICALDEMOGRAPHIC
                inner join dbo.WPBIOGRAPHICALDEMOGRAPHIC SOURCEBIOGRAPHICALDEMOGRAPHIC ON SOURCEBIOGRAPHICALDEMOGRAPHIC.PARTIALHASH = TARGETBIOGRAPHICALDEMOGRAPHIC.PARTIALHASH
                where SOURCEBIOGRAPHICALDEMOGRAPHIC.WEALTHID = @SOURCEID
                    and TARGETBIOGRAPHICALDEMOGRAPHIC.WEALTHID = @TARGETID
                    and not (TARGETBIOGRAPHICALDEMOGRAPHIC.CONFIRMED = 1 or TARGETBIOGRAPHICALDEMOGRAPHIC.REJECTED = 1)
                    and (SOURCEBIOGRAPHICALDEMOGRAPHIC.CONFIRMED = 1 or SOURCEBIOGRAPHICALDEMOGRAPHIC.REJECTED = 1)
                    and SOURCEBIOGRAPHICALDEMOGRAPHIC.PARTIALHASH <> ''

                -- For all other cases when Source and Target match on hash codes, only notes will be appended.

                update TARGETBIOGRAPHICALDEMOGRAPHIC
                set
                    [CNOTES] = TARGETBIOGRAPHICALDEMOGRAPHIC.CNOTES + (case when TARGETBIOGRAPHICALDEMOGRAPHIC.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCEBIOGRAPHICALDEMOGRAPHIC.CNOTES
                    ,[CHANGEDBYID] = @CHANGEAGENTID
                    ,[DATECHANGED] = @CURRENTDATE
                from dbo.WPBIOGRAPHICALDEMOGRAPHIC TARGETBIOGRAPHICALDEMOGRAPHIC
                inner join dbo.WPBIOGRAPHICALDEMOGRAPHIC SOURCEBIOGRAPHICALDEMOGRAPHIC ON SOURCEBIOGRAPHICALDEMOGRAPHIC.PARTIALHASH = TARGETBIOGRAPHICALDEMOGRAPHIC.PARTIALHASH
                where SOURCEBIOGRAPHICALDEMOGRAPHIC.WEALTHID = @SOURCEID
                    and TARGETBIOGRAPHICALDEMOGRAPHIC.WEALTHID = @TARGETID
                    and ((TARGETBIOGRAPHICALDEMOGRAPHIC.CONFIRMED = 1 or TARGETBIOGRAPHICALDEMOGRAPHIC.REJECTED = 1)
                        or (SOURCEBIOGRAPHICALDEMOGRAPHIC.CONFIRMED = 0 and SOURCEBIOGRAPHICALDEMOGRAPHIC.REJECTED = 0))
                    and SOURCEBIOGRAPHICALDEMOGRAPHIC.PARTIALHASH <> ''
                    and SOURCEBIOGRAPHICALDEMOGRAPHIC.CNOTES <> ''

                -- If unique record in Source that came from wealth screening (will have hash code), move it to Target.

                update SOURCEBIOGRAPHICALDEMOGRAPHIC
                set
                    [WEALTHID] = @TARGETID,
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CURRENTDATE
                from dbo.WPBIOGRAPHICALDEMOGRAPHIC SOURCEBIOGRAPHICALDEMOGRAPHIC
                left join dbo.WPBIOGRAPHICALDEMOGRAPHIC TARGETBIOGRAPHICALDEMOGRAPHIC ON TARGETBIOGRAPHICALDEMOGRAPHIC.PARTIALHASH = SOURCEBIOGRAPHICALDEMOGRAPHIC.PARTIALHASH and TARGETBIOGRAPHICALDEMOGRAPHIC.WEALTHID = @TARGETID
                where SOURCEBIOGRAPHICALDEMOGRAPHIC.WEALTHID = @SOURCEID
                    and (SOURCEBIOGRAPHICALDEMOGRAPHIC.PARTIALHASH is not null and SOURCEBIOGRAPHICALDEMOGRAPHIC.PARTIALHASH <> '')
                    and TARGETBIOGRAPHICALDEMOGRAPHIC.ID is null

                -- If unique record in Source which is entered manually, move it to Target.

                update dbo.WPBIOGRAPHICALDEMOGRAPHIC
                set
                    [WEALTHID] = @TARGETID,
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CURRENTDATE
                where WEALTHID = @SOURCEID
                    and (PARTIALHASH is null or PARTIALHASH = '')
            end