USP_MERGETASK_CONSTITUENTSTAFF

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

                    declare @CHANGEDATE datetime = getdate();

                    --Bring over any source staff records that don't have date ranges

                    --that overlap with existing staff records on the target

                    update dbo.STAFFDATERANGE
                    set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                    where CONSTITUENTID = @SOURCEID
                    and ID not in
                    (
                        select source.ID
                        from dbo.STAFFDATERANGE source
                        cross apply dbo.STAFFDATERANGE target
                        where target.CONSTITUENTID = @TARGETID
                        and source.CONSTITUENTID = @SOURCEID
                        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 staff record on the source that is "open"

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

                    --recent staff record on the target is open.  This is done

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

                    --overlapping date ranges.

                    if exists
                    (
                        select top(1) ID
                        from dbo.STAFFDATERANGE
                        where CONSTITUENTID = @SOURCEID
                        and DATETO is null
                    )
                    begin
                        -- 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.

                        declare @openStaffID uniqueidentifier;
                        select @openStaffID = ID
                        from dbo.STAFFDATERANGE
                        where CONSTITUENTID = @TARGETID
                        and DATETO is null;

                        if @openStaffID is null
                        begin
                            -- If no open staff record was found for the Target, 

                            -- then "open" the record with the most recent DATETO field

                            update dbo.STAFFDATERANGE
                            set DATETO = null, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                            where ID in
                            (
                                select top(1) ID
                                from dbo.STAFFDATERANGE
                                where CONSTITUENTID = @TARGETID
                                order by DATEFROM desc
                            )
                        end
                    end

                    return 0;