USP_MERGETASK_CONSTITUENTCONSTITUENCY

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

                    declare @CHANGEDATE datetime = getdate();

                    update dbo.CONSTITUENCY
                    set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                    where CONSTITUENTID = @SOURCEID
                    and ID not in
                    (
                        select source.ID
                        from dbo.CONSTITUENCY source
                        cross apply dbo.CONSTITUENCY target
                        where target.CONSTITUENTID = @TARGETID
                        and source.CONSTITUENTID = @SOURCEID
                        and target.CONSTITUENCYCODEID = source.CONSTITUENCYCODEID
                        and
                        (
                            (target.DATETO between source.DATEFROM and source.DATETO) or
                            (source.DATETO between target.DATEFROM and target.DATETO) or
                            (target.DATEFROM between source.DATEFROM and source.DATETO) or
                            (source.DATEFROM between target.DATEFROM and target.DATETO) or

                            (target.DATEFROM is null and source.DATEFROM <= target.DATETO) or
                            (source.DATEFROM is null and target.DATEFROM <= source.DATETO) or
                            (target.DATETO is null and source.DATETO >= target.DATEFROM) or
                            (source.DATETO is null and target.DATETO >= source.DATEFROM) or

                            (source.DATEFROM is null and target.DATEFROM is null) or
                            (source.DATETO is null and  target.DATETO is null) or
                            (source.DATEFROM is null and source.DATETO is null) or
                            (target.DATEFROM is null and target.DATETO is null)
                        )
                    )

                    --If there is still a constituency record on the source that is "open"

                    --(i.e. it's DATETO field is null) then make sure the most

                    --recent constituency record of that type on the target is open.  This is done

                    --to prevent "open" constituency status of being lost due to

                    --overlapping date ranges.

                    declare @openCOnstituencyID uniqueidentifier;
                    declare @tConstituencyID uniqueidentifier;
                    declare @tConstituencyCodeID uniqueidentifier;

                    -- Find all of the Target constituent's constituency records

                    -- where an open constituency of the same type still exists

                    -- on the Source.

                    declare target_constituencyCursor cursor local fast_forward for
                        select target.ID, target.CONSTITUENCYCODEID
                        from dbo.CONSTITUENCY source
                        cross apply dbo.CONSTITUENCY target
                        where source.CONSTITUENTID = @SOURCEID
                        and target.CONSTITUENTID = @TARGETID
                        and target.CONSTITUENCYCODEID = source.CONSTITUENCYCODEID
                        and source.DATETO is null;

                    create table #merge_target_newestConstituencies(constituencyID uniqueidentifier, constituencyCodeID uniqueidentifier);

                    open target_constituencyCursor;
                    fetch next from target_constituencyCursor into @tConstituencyID, @tConstituencyCodeID;
                    while @@fetch_status = 0
                    begin
                        -- first check to see if the current record's

                        -- constituency code has already been handled

                        if not exists 
                        (
                            select constituencyID 
                            from #merge_target_newestConstituencies
                            where constituencyCodeID = @tConstituencyCodeID
                        )
                        begin
                            -- check to see if the target already has an open

                            -- constituency of the current type.

                            -- Order By clause evaluates NULL as less than any value.

                            -- Since we prefer null to any actual date when looking for

                            -- the most recent record, we have to look for NULL as a

                            -- separate search.

                            select @openConstituencyID = ID
                            from dbo.CONSTITUENCY
                            where CONSTITUENCYCODEID = @tConstituencyCodeID
                            and CONSTITUENTID = @TARGETID
                            and DATETO is null

                            if @openConstituencyID is null
                            begin
                                -- If no open constituency of the current type was found,

                                -- store the record with the most recent DATETO field

                                insert into #merge_target_newestConstituencies(constituencyID, constituencyCodeID)
                                select top(1) ID, CONSTITUENCYCODEID
                                from dbo.CONSTITUENCY
                                where CONSTITUENTID = @TARGETID
                                and CONSTITUENCYCODEID = @tConstituencyCodeID
                                order by DATETO desc
                            end
                            else
                            begin
                                -- If an open constituency exists for the target, store that one instead.

                                -- Technically, no further operation is necessary for this case, but

                                -- storing the record will yield better performance since it will save us

                                -- from repeating the search for subsequent records of the same constituency type.

                                insert into #merge_target_newestConstituencies(constituencyID, constituencyCodeID)
                                values(@openConstituencyID, @tConstituencyCodeID)            
                            end
                        end

                        --clear the open constituency variable

                        set @openConstituencyID = null;

                        --fetch the next row from the cursor

                        fetch next from target_constituencyCursor into @tConstituencyID, @tConstituencyCodeID;
                    end

                    --Now "open" all of the selected Target constituencies by setting their

                    --DATETO fields to null

                    update dbo.CONSTITUENCY
                    set DATETO = null, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                    where ID in (select constituencyID from #merge_target_newestConstituencies)

                    close target_constituencyCursor;
                    deallocate target_constituencyCursor;
                    drop table #merge_target_newestConstituencies;

                    --JamesWill WI179048 2011-11-08 If the source is a relationship manager for a constituent, make the target a relationship manager for 

                    --    that constituent as well. Don't do this if target is already a relationship manager for that constituent.


                    insert into dbo.RELATIONSHIPMANAGER(CONSTITUENTID, FUNDRAISERID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        select
                            RELATIONSHIPMANAGER.CONSTITUENTID,
                            @TARGETID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CHANGEDATE,
                            @CHANGEDATE
                        from dbo.RELATIONSHIPMANAGER
                        left join dbo.RELATIONSHIPMANAGER RELATIONSHIPMANAGER_TARGET 
                            on RELATIONSHIPMANAGER_TARGET.CONSTITUENTID = RELATIONSHIPMANAGER.CONSTITUENTID
                            and RELATIONSHIPMANAGER_TARGET.FUNDRAISERID = @TARGETID
                        where 
                            RELATIONSHIPMANAGER.FUNDRAISERID = @SOURCEID 
                            and RELATIONSHIPMANAGER_TARGET.ID is null;


                    --JamesWill WI179048 2011-11-08 Make it so that the source is no longer a relationship manager for anyone.

                    declare @CONTEXTCACHE varbinary(128);
                    set @CONTEXTCACHE = CONTEXT_INFO();

                    if not @CHANGEAGENTID is null
                        set CONTEXT_INFO @CHANGEAGENTID;

                    delete dbo.RELATIONSHIPMANAGER where FUNDRAISERID = @SOURCEID;

                    if not @CONTEXTCACHE is null
                        set CONTEXT_INFO @CONTEXTCACHE;

                    return 0;