Hi, below is the approach, i tried. with cte_endDate as ( Select distinct A.EmpID, A.EndDate from DimEmployee A LEFT JOIN DimEmployee B ON A.EmpID = B.EmpID AND A.EndDate >= B.StartDate AND A.EndDate < B.EndDate WHERE B.EmpID IS NULL ) , cte_startDate as ( select distinct EmpID, StartDate from DimEmployee ) , cte_Min as ( Select SD.EmpID, SD.StartDate, MIN(ED.EndDate) as EndDate from cte_startDate SD INNER JOIN cte_endDate ED ON SD.EmpID = ED.EmpID AND SD.StartDate < ED.EndDate group by SD.EmpID, SD.StartDate ) Select EmpID, MIN(StartDate) as StartDate, EndDate from cte_Min group by EmpID, EndDate Order by 1,2;