SQL | How to Merge Overlapping Date Ranges | SQL Interview Questions And Answers

In this SQL Tutorial, we write a SQL Query to Merge Overlapping Date Ranges.
It is also a common SQL Interview Question

Must Do Data Analytics Certifications -

Google Data Analytics Professional Certificate

Google Advanced Data Analytics Professional Certificate

Practice SQL Queries on Your Browser through Interactive Courses -

Please do not forget to like, subscribe and share.

For enrolling and enquiries, please contact us at

[momuscomus]

Why not working on these intervals of one emploee:

2025-01-05 .. 2025-01-08
2025-01-04 .. 2025-01-12
2025-01-11 .. 2025-01-15

[FromPlanetZX]

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;