USP_MERGETASK_CONSTITUENTCOMMITTEE

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

                    declare @CURRENTDATE datetime = getdate();

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

                    --that overlap with existing committee records on the target

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

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

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

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

                    --overlapping date ranges.

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

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

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

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

                    update TARGET set
                        TARGET.CANCOORDINATEEVENTS = case when TARGET.CANCOORDINATEEVENTS = 1 or [SOURCE].CANCOORDINATEEVENTS = 1 then 1 else 0 end,
                        TARGET.CANSOLICITREVENUE = case when TARGET.CANSOLICITREVENUE = 1 or [SOURCE].CANSOLICITREVENUE = 1 then 1 else 0 end,
                        TARGET.CANSETCOMMITTEEGOALS = case when TARGET.CANSETCOMMITTEEGOALS = 1 or [SOURCE].CANSETCOMMITTEEGOALS = 1 then 1 else 0 end,
                        TARGET.DATECHANGED = @CURRENTDATE,
                        TARGET.CHANGEDBYID = @CHANGEAGENTID
                    from dbo.COMMITTEE TARGET
                    inner join dbo.COMMITTEE [SOURCE] on [SOURCE].ID = @sourceid
                    where TARGET.ID = @TARGETID

                    --Copy all group goals that don't exist on the target

                    update GROUPGOAL set
                        GROUPID = @TARGETID,
                        DATECHANGED = @CURRENTDATE,
                        CHANGEDBYID = @CHANGEAGENTID
                    from dbo.GROUPGOAL SG
                    where GROUPID = @SOURCEID
                    and not exists
                    (
                        select SG.ID
                        from dbo.GROUPGOAL TG
                        where TG.GROUPID = @TARGETID
                        and (SG.ISFUNDRAISINGGOAL = TG.ISFUNDRAISINGGOAL
            and SG.NAME = TG.NAME
                        and ((SG.GROUPGOALUNITCODEID = TG.GROUPGOALUNITCODEID) 
                            or (SG.GROUPGOALUNITCODEID is null and TG.GROUPGOALUNITCODEID is null)))                       
                    )

          update CAMPAIGNFUNDRAISER set
            CONSTITUENTID = @TARGETID,
            DATECHANGED = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID
          from dbo.CAMPAIGNFUNDRAISER SCF
          where CONSTITUENTID = @SOURCEID
          and not exists
          (
            select SCF.ID
            from dbo.CAMPAIGNFUNDRAISER TCF
            where TCF.CONSTITUENTID = @TARGETID
              and TCF.CAMPAIGNID = SCF.CAMPAIGNID
              and ((SCF.DATEFROM is null and TCF.DATEFROM is null)
                  or (SCF.DATETO is null and TCF.DATETO is null)
                  or (SCF.DATEFROM <= TCF.DATEFROM and (SCF.DATETO >= TCF.DATEFROM or SCF.DATETO is null))
                  or (TCF.DATEFROM <= SCF.DATEFROM and (TCF.DATETO >= SCF.DATEFROM or TCF.DATETO is null))
                  or (SCF.DATEFROM is null and (SCF.DATETO >= TCF.DATEFROM or SCF.DATETO is null))
                  or (TCF.DATEFROM is null and (TCF.DATETO >= SCF.DATEFROM or TCF.DATETO is null)))
          )

                    return 0;