USP_MERGETASK_CONSTITUENTBOARDMEMBER

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

                    declare @CHANGEDATE datetime = getdate();

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

                    --that overlap with existing board member records on the target

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

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

                    --recent board member record on the target is open.  This is done

                    --to prevent "open" board member status of being lost due to

                    --overlapping date ranges.                    

                    if exists
                    (
                        select top(1) ID
                        from dbo.BOARDMEMBERDATERANGE
                        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 @openBoardMemberID uniqueidentifier;
                        select @openBoardMemberID = ID
                        from dbo.BOARDMEMBERDATERANGE
                        where CONSTITUENTID = @TARGETID
                        and DATETO is null;

                        if @openBoardMemberID is null
                        begin
                            -- If no open board membership was found for the Target, 

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

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

                    return 0;