how to get only overlapping records based on start and end date?

Question

I have a table and query as follow: I am trying to get overlapping records between [Assignment Start Date] and [Assignment End Date] for same Employee Id. In short I need data for those Employee Id which are allocated for same time period or overlapping time period from below example. e.g.

  [Employee Id]   [Assignment Start Date]     [Assignment End Date]   [Allocation Percentage]

        100                2016-03-01                 2017-02-28             100
        102                2016-06-01                 2016-12-31             100
        102                2016-07-01                 2016-10-30             100
        102                2016-11-01                 2017-01-31             100
        103                2017-02-01                 2017-05-30             100
        102                2017-04-01                 2017-06-30             100
        102                2017-11-01                 2017-01-31             100
        104                2017-02-01                 2017-05-01             100



CREATE TABLE #Result
(
PK INT IDENTITY(1,1),
[BU] VARCHAR(20),
[Division] VARCHAR(20),
[Product Name] VARCHAR(30),
[Employee ID] NVARCHAR(20),
[Resource Name] VARCHAR(50),
[Resource_ID] INT,
[Assignment Start Date] DATE,
[Assignment End Date] DATE,
[Allocation Percentage] INT,
[Location] VARCHAR(100),
[Development Manager] VARCHAR(50),
[Allocation] VARCHAR(20)
);


SELECT  DISTINCT r1.PK, r1.Resource_ID,r1.[Employee ID], r1.[Assignment Start Date] AS 'Start 1' ,r1.[Assignment End Date] AS 'End 1' , r2.[Assignment Start Date] , r2.[Assignment End Date]
INTO #temp1
FROM #Result r1

INNER JOIN #Result r2
ON r1.[Employee ID] = r2.[Employee ID]
AND (r1.PK <> r2.PK)
AND ((r1.[Assignment Start Date] <= r2.[Assignment Start Date]) AND (r1.[Assignment End Date] >= r2.[Assignment Start Date]))
OR ((r1.[Assignment Start Date] > r2.[Assignment Start Date] AND r1.[Assignment Start Date] <= r2.[Assignment End Date]) AND (r1.[Assignment End Date] <= r2.[Assignment End Date]))
OR ((r1.[Assignment Start Date] > r2.[Assignment Start Date] AND r1.[Assignment Start Date]  <= r2.[Assignment End Date]) AND (r1.[Assignment End Date] > r2.[Assignment End Date]))
OR (r1.[Assignment Start Date]  = r2.[Assignment End Date]) 

I have tried using above query but it gives all records related to overlapping employee even if that entry for that employee is not overlapping.

In above example there is a Employee Id 102 which has 2 overlapping entries and 3rd entry which is not overlapping I want to remove it from this result. please help.

Answer 1

CTE, row_number and a self join

with CTE as
(
select PK, Resource_ID, [Employee ID] as Emp_ID, [Assignment Start Date] as s_Date, [Assignment End Date] as e_date,
       row_number() over(partition by [Employee ID] order by  [Assignment Start Date] ) as rn
from #Result
)
select t1.*, t2.*
from CTE t1
inner join CTE t2
  on t1.Resource_ID = t2.Resource_ID
  and t1.Emp_ID = t2.Emp_ID
  and t2.rn = t1.rn +1
where t2.s_date <= t1.e_date 
or t1.e_date is null -- allows for null end date