USP_MERGETASK_CONSTITUENTEDUCATION

                CREATE procedure dbo.USP_MERGETASK_CONSTITUENTEDUCATION
                (
                    @SOURCEID uniqueidentifier,
                    @TARGETID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier,
                    @PRIMARYCRITERIA int = 0,
                    @DELETEDUPES bit = 0,
                    @MERGEEDUCATIONALINVOLVEMENT bit = 0,
                    @DELETEEDUCATIONALINVOLVEMENTDUPES bit = 0
                )
                as
                    set nocount on;

                    declare @CHANGEDATE datetime = getdate();

                    --Store the primary education record id's for later access
                    declare @SOURCEPRIMARYID as uniqueidentifier;
                    select @SOURCEPRIMARYID = ID
                    from dbo.EDUCATIONALHISTORY
                    where CONSTITUENTID = @SOURCEID and ISPRIMARYRECORD = 1;

                    declare @TARGETPRIMARYID as uniqueidentifier;
                    select @TARGETPRIMARYID = ID
                    from dbo.EDUCATIONALHISTORY
                    where CONSTITUENTID = @TARGETID and ISPRIMARYRECORD = 1;

                    --Wipe the primary record indicators before the merge
                    --to keep from violating any constraints
                    update dbo.EDUCATIONALHISTORY
                    set ISPRIMARYRECORD = 0, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                    where CONSTITUENTID = @SOURCEID or CONSTITUENTID = @TARGETID;

                    --move all of the source constit's education records over to
                    --the target constit
                    if @DELETEDUPES = 0
                        update dbo.EDUCATIONALHISTORY
                        set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        where CONSTITUENTID = @SOURCEID
                    else
                        --Omit duplicate educational records if the @DELETEDUPES
                        --flag is set
                        update dbo.EDUCATIONALHISTORY
                        set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        where CONSTITUENTID = @SOURCEID
                        and ID not in
                        (
                            select a.ID
                            from dbo.EDUCATIONALHISTORY a
                            inner join dbo.EDUCATIONALHISTORY b
                            on a.EDUCATIONALINSTITUTIONID = b.EDUCATIONALINSTITUTIONID
                            --and (a.EDUCATIONALHISTORYTYPECODEID = b.EDUCATIONALHISTORYTYPECODEID or (a.EDUCATIONALHISTORYTYPECODEID is null and b.EDUCATIONALHISTORYTYPECODEID is null))
                            and (a.CONSTITUENCYSTATUS = b.CONSTITUENCYSTATUS or (a.CONSTITUENCYSTATUS is null and b.CONSTITUENCYSTATUS is null))
                            --and (a.EDUCATIONALDEGREECODEID = b.EDUCATIONALDEGREECODEID or (a.EDUCATIONALDEGREECODEID is null and b.EDUCATIONALDEGREECODEID is null))
                            and ( a.ACADEMICCATALOGPROGRAMID = b.ACADEMICCATALOGPROGRAMID or (a.ACADEMICCATALOGPROGRAMID is null and b.ACADEMICCATALOGPROGRAMID is null) )
                            and ( a.EDUCATIONALPROGRAMCODEID = b.EDUCATIONALPROGRAMCODEID or (a.EDUCATIONALPROGRAMCODEID is null and b.EDUCATIONALPROGRAMCODEID is null) )
                            and ( a.ACADEMICCATALOGDEGREEID = b.ACADEMICCATALOGDEGREEID or (a.ACADEMICCATALOGDEGREEID is null and b.ACADEMICCATALOGDEGREEID is null) )
                            and ( a.EDUCATIONALDEGREECODEID = b.EDUCATIONALDEGREECODEID or (a.EDUCATIONALDEGREECODEID is null and b.EDUCATIONALDEGREECODEID is null) )
                            and a.STARTDATE = b.STARTDATE
                            and a.ENDDATE = b.ENDDATE
                            and a.CLASSOF = b.CLASSOF
                            where a.CONSTITUENTID = @SOURCEID
                            and b.CONSTITUENTID = @TARGETID
                        )

                    --select the primary educational record
                    if @PRIMARYCRITERIA = 0 and @TARGETPRIMARYID is not null
                    begin
                        -- The target's original primary education record
                        -- is assured of being associated with the target
                        -- so simply reset it's primary indicator.
                        update dbo.EDUCATIONALHISTORY
                        set ISPRIMARYRECORD = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        where ID = @TARGETPRIMARYID
                    end
                    else
                    begin
                        -- The target had no primary education record,
                        -- or we are preserving the source's primary
                        -- education record
                        if @SOURCEPRIMARYID is null
                        begin
                            -- If the source had no primary education record then
                            -- attempt to reset the target's primary record indicator
                            update dbo.EDUCATIONALHISTORY
                            set ISPRIMARYRECORD = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                            where ID = @TARGETPRIMARYID                            
                        end
                        else
                        begin
                            -- Otherwise, the source had a primary educational record
                            if @DELETEDUPES = 0
                            begin
                                -- If we are not deleting duplicate education info
                                -- then we can be assured the source's primary
                                -- education info is associated with the target, so
                                -- simply reset its primary indicator
                                update dbo.EDUCATIONALHISTORY
                                set ISPRIMARYRECORD = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                                where ID = @SOURCEPRIMARYID
                            end
                            else
                            begin
                                declare @TEMPID uniqueidentifier;
                                select @TEMPID = CONSTITUENTID
                                from dbo.EDUCATIONALHISTORY
                                where ID = @SOURCEPRIMARYID;

                                if @TEMPID = @TARGETID
                                begin
                                    -- If the source primary education is now associated
                                    -- with the target, then reset its primary indicator
                                    update dbo.EDUCATIONALHISTORY
                                    set ISPRIMARYRECORD = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                                    where ID = @SOURCEPRIMARYID;                        
                                end
                                else
                                begin
                                    -- Otherwise, the source's primary education was
                                    -- not associated with the target b/c it is a
                                    -- duplicate of one of the target's education records.
                                    -- In this scenario, the target's education record that
                                    -- is a duplicate of the source's primary education
                                    -- should be set as the target's primary education.
                                    set @TEMPID = null;

                                    select @TEMPID = b.ID
                                    from dbo.EDUCATIONALHISTORY a
                                    inner join dbo.EDUCATIONALHISTORY b
                                    on a.EDUCATIONALINSTITUTIONID = b.EDUCATIONALINSTITUTIONID
                                    --and (a.EDUCATIONALHISTORYTYPECODEID = b.EDUCATIONALHISTORYTYPECODEID or (a.EDUCATIONALHISTORYTYPECODEID is null and b.EDUCATIONALHISTORYTYPECODEID is null))
                                    and (a.CONSTITUENCYSTATUS = b.CONSTITUENCYSTATUS or (a.CONSTITUENCYSTATUS is null and b.CONSTITUENCYSTATUS is null))
                                    --and (a.EDUCATIONALDEGREECODEID = b.EDUCATIONALDEGREECODEID or (a.EDUCATIONALDEGREECODEID is null and b.EDUCATIONALDEGREECODEID is null))
                                    and ( a.ACADEMICCATALOGPROGRAMID = b.ACADEMICCATALOGPROGRAMID or (a.ACADEMICCATALOGPROGRAMID is null and b.ACADEMICCATALOGPROGRAMID is null) )
                                    and ( a.EDUCATIONALPROGRAMCODEID = b.EDUCATIONALPROGRAMCODEID or (a.EDUCATIONALPROGRAMCODEID is null and b.EDUCATIONALPROGRAMCODEID is null) )
                                    and ( a.ACADEMICCATALOGDEGREEID = b.ACADEMICCATALOGDEGREEID or (a.ACADEMICCATALOGDEGREEID is null and b.ACADEMICCATALOGDEGREEID is null) )
                                    and ( a.EDUCATIONALDEGREECODEID = b.EDUCATIONALDEGREECODEID or (a.EDUCATIONALDEGREECODEID is null and b.EDUCATIONALDEGREECODEID is null) )
                                    and a.STARTDATE = b.STARTDATE
                                    and a.ENDDATE = b.ENDDATE
                                    and a.CLASSOF = b.CLASSOF
                                    where a.ID = @SOURCEPRIMARYID
                                    and b.CONSTITUENTID = @TARGETID;

                                    if @TEMPID is not null
                                    begin
                                        update dbo.EDUCATIONALHISTORY
                                        set ISPRIMARYRECORD = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                                        where ID = @TEMPID;
                                    end
                                    else
                                    begin
                                        -- If no match was found then reset the primary
                                        -- indicator on the target's original primary
                                        -- record
                                        update dbo.EDUCATIONALHISTORY
                                        set ISPRIMARYRECORD = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                                        where ID = @TARGETPRIMARYID
                                    end
                                end
                            end
                        end                        
                    end                    

                    if @MERGEEDUCATIONALINVOLVEMENT = 1
                    begin
                        if @DELETEEDUCATIONALINVOLVEMENTDUPES = 0
                        begin
                            update dbo.EDUCATIONALINVOLVEMENT
                            set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                            where CONSTITUENTID = @SOURCEID
                        end
                        else
                        begin
                            update dbo.EDUCATIONALINVOLVEMENT
                            set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                            where CONSTITUENTID = @SOURCEID
                            and ID not in
                            (
                                select EDUCATIONALINVOLVEMENTSOURCE.ID
                                from dbo.EDUCATIONALINVOLVEMENT EDUCATIONALINVOLVEMENTSOURCE
                                    inner join dbo.EDUCATIONALINVOLVEMENT EDUCATIONALINVOLVEMENTTARGET
                                        on EDUCATIONALINVOLVEMENTSOURCE.EDUCATIONALINVOLVEMENTTYPECODEID = EDUCATIONALINVOLVEMENTTARGET.EDUCATIONALINVOLVEMENTTYPECODEID
                                        and EDUCATIONALINVOLVEMENTSOURCE.EDUCATIONALINVOLVEMENTNAMEID = EDUCATIONALINVOLVEMENTTARGET.EDUCATIONALINVOLVEMENTNAMEID
                                        and EDUCATIONALINVOLVEMENTSOURCE.DATEFROM = EDUCATIONALINVOLVEMENTTARGET.DATEFROM
                                        and EDUCATIONALINVOLVEMENTSOURCE.DATETO = EDUCATIONALINVOLVEMENTTARGET.DATETO
                                        and (EDUCATIONALINVOLVEMENTSOURCE.EDUCATIONALINSTITUTIONID = EDUCATIONALINVOLVEMENTTARGET.EDUCATIONALINSTITUTIONID 
                                            or (EDUCATIONALINVOLVEMENTSOURCE.EDUCATIONALINSTITUTIONID is null and EDUCATIONALINVOLVEMENTTARGET.EDUCATIONALINSTITUTIONID is null))
                                        and EDUCATIONALINVOLVEMENTSOURCE.COMMENT = EDUCATIONALINVOLVEMENTTARGET.COMMENT
                                        and (EDUCATIONALINVOLVEMENTSOURCE.EDUCATIONALINVOLVEMENTAWARDCODEID = EDUCATIONALINVOLVEMENTTARGET.EDUCATIONALINVOLVEMENTAWARDCODEID
                                            or (EDUCATIONALINVOLVEMENTSOURCE.EDUCATIONALINVOLVEMENTAWARDCODEID is null and EDUCATIONALINVOLVEMENTTARGET.EDUCATIONALINVOLVEMENTAWARDCODEID is null))
                                where EDUCATIONALINVOLVEMENTSOURCE.CONSTITUENTID = @SOURCEID
                                and EDUCATIONALINVOLVEMENTTARGET.CONSTITUENTID = @TARGETID
                            )
                        end
                    end

                    return 0;