USP_MERGETASK_DEMOGRAPHIC

CREATE procedure dbo.USP_MERGETASK_DEMOGRAPHIC
(
    @SOURCEID uniqueidentifier,
    @TARGETID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as
    set nocount on;

    declare @SOURCEETHNICITYCODEID uniqueidentifier = null;                    
    declare @SOURCERELIGIONCODEID uniqueidentifier = null;
    declare @SOURCETARGETCODEID uniqueidentifier = null;
    declare @SOURCEINCOMECODEID uniqueidentifier = null;
    declare @SOURCEBIRTHPLACE nvarchar(50) = '';
    declare @SOURCEDENOMINATIONCODEID uniqueidentifier = null;
    declare @SOURCECHURCHAFFILIATIONCODEID uniqueidentifier = null;
    declare @SOURCEISHISPANICLATINO bit = 0;

    declare @DATECHANGED datetime = getdate();


    --Cache the Source record's field values

    select 
        @SOURCEETHNICITYCODEID = ETHNICITYCODEID,
        @SOURCERELIGIONCODEID = RELIGIONCODEID,
        @SOURCETARGETCODEID = TARGETCODEID,
        @SOURCEINCOMECODEID = INCOMECODEID,
        @SOURCEBIRTHPLACE = BIRTHPLACE,
        @SOURCEDENOMINATIONCODEID = DENOMINATIONCODEID,
        @SOURCECHURCHAFFILIATIONCODEID = CHURCHAFFILIATIONCODEID,
        @SOURCEISHISPANICLATINO = ISHISPANICLATINO
    from dbo.DEMOGRAPHIC
    where ID = @SOURCEID                    

    -- write sql to handle merging the data for the given source and target IDs


    if exists(select ID from DEMOGRAPHIC where id = @TARGETID)
    begin
        update dbo.DEMOGRAPHIC
        set
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @DATECHANGED,
            ETHNICITYCODEID = case when (ETHNICITYCODEID is null) then @SOURCEETHNICITYCODEID else ETHNICITYCODEID end,
            RELIGIONCODEID = case when (RELIGIONCODEID is null) then @SOURCERELIGIONCODEID else RELIGIONCODEID end,
            TARGETCODEID = case when (TARGETCODEID is null) then @SOURCETARGETCODEID else TARGETCODEID end,
            INCOMECODEID = case when (INCOMECODEID is null) then @SOURCEINCOMECODEID else INCOMECODEID end,
            BIRTHPLACE = case when (BIRTHPLACE = '') then @SOURCEBIRTHPLACE else BIRTHPLACE end,
            DENOMINATIONCODEID = case when (DENOMINATIONCODEID is null) then @SOURCEDENOMINATIONCODEID else DENOMINATIONCODEID end,
            CHURCHAFFILIATIONCODEID = case when (CHURCHAFFILIATIONCODEID is null) then @SOURCECHURCHAFFILIATIONCODEID else CHURCHAFFILIATIONCODEID end
        where 
            ID = @TARGETID
    end
    else
    begin
        insert into dbo.DEMOGRAPHIC (ID, ADDEDBYID, DATEADDED, CHANGEDBYID, DATECHANGED, 
            ETHNICITYCODEID, RELIGIONCODEID, TARGETCODEID, INCOMECODEID, BIRTHPLACE, DENOMINATIONCODEID, CHURCHAFFILIATIONCODEID, ISHISPANICLATINO) 
        values (@TARGETID, @CHANGEAGENTID, @DATECHANGED, @CHANGEAGENTID, @DATECHANGED, 
            @SOURCEETHNICITYCODEID, @SOURCERELIGIONCODEID, @SOURCETARGETCODEID, @SOURCEINCOMECODEID, @SOURCEBIRTHPLACE, @SOURCEDENOMINATIONCODEID, @SOURCECHURCHAFFILIATIONCODEID, @SOURCEISHISPANICLATINO)
    end


    -- copy over any Ethnicities not already set on Target

    insert into dbo.DEMOGRAPHICETHNICITY
        (DEMOGRAPHICID, ETHNICITYCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select 
        @TARGETID, ETHNICITYCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATECHANGED, @DATECHANGED
    from 
        dbo.DEMOGRAPHICETHNICITY
    where
        DEMOGRAPHICETHNICITY.DEMOGRAPHICID = @SOURCEID
        and DEMOGRAPHICETHNICITY.ETHNICITYCODEID not in (select ETHNICITYCODEID from dbo.DEMOGRAPHICETHNICITY where DEMOGRAPHICID = @TARGETID)


    return 0;