SQL Query | How to identify Overlapping Date Ranges

This video discusses the SQL Query to identify overlapping date ranges in a table. This scenario is also valid for Slowly Changing Dimension (SCD) Type 2 tables.

Best Online courses -
Beginners to Python Programming

Data Science and Business Analytics with Python

Get 40% OFF of Skillshare with code FLASH40 - October 2022

Please do not forget to like, subscribe and share.

For enrolling and enquiries, please contact us at

[Ravi-things]

how will i implement for more than 2 cases ...for example i have data with 100 rows with different names and some of the names was repeated . how can i find that for same names with dates were overlapped..please suggest

[yoyoyoyoll]

Thanks so much for the video. It helps a lot on my query!

[nikolaybaranov]

Thanks! But best practices is using cross join rather than a comma

[MK-lhxd]

Hi,
I have been going through your videos and find them very good. Thank you for consolidating the interview questions as a easy-to-refer video series.
In this video however, I think the logic is not correct, unless I misunderstand the problem. I tried the given solution and did not get the correct result set. Given below is the test data I used:
create table ContractEmployees(
name varchar(40),
start_date date,
end_date date
)
;
truncate ContractEmployees;
insert into ContractEmployees(name, start_date, end_date) values
('Guy', '2022-10-12', '2022-10-22' ),
('Guy', '2022-10-20', '2022-10-30'),
('Kevin', '2021-02-20', '2021-12-30'),
('Kevin', '2022-01-01', NULL),
('Roberto', '2022-03-01', '2022-04-30'),
('Roberto', '2022-04-01', '2022-04-10'),
('Rob', '2022-05-23', NULL),
('Rob', '2022-01-20', '2022-05-23'),
('Thiery', '2021-02-26', '2021-04-21'),
('Thiery', '2021-04-22', '2021-08-21'),
('David', '2021-10-17', '2021-11-16'),
('David', '2021-10-17', '2021-11-16'),
('JoLynn', '2021-02-16', NULL),
('Ruth', '2022-02-28', NULL)
;
My solution is given below:

with contract_emps_with_rnums as (
select
*,
row_number() over() as rnum
from
ContractEmployees a
)
select
*
from
contract_emps_with_rnums a
join contract_emps_with_rnums b on a.name = b.name
where
a.rnum <> b.rnum
and (a.start_date between b.start_date and b.end_date
or a.end_date between b.start_date and b.end_date
)
;
When you have a chance, let me know if my understanding is correct.

[surajpatil]

Thanks... if we have apply self join on larger table will it take too much time fetch records?{Performancewise}

[M_S_O_Influence_Gaming]

is there any way to find overlap from the same table? So, without joining another table..

[Ravi-things]

sample data like that

id name start_date end_date
1 Robert 2009-01-16 2009-01-20
2 JOHN 2010-06-24 2010-06-26
3 Robert 2009-01-18 2009-01-20

i am expected result like that

id name start_date end_date
1 Robert 2009-01-16 2009-01-20
3 Robert 2009-01-18 2009-01-20

[shreyashchoudhary]

please provide data file for this, also attach insert or data file, create script , watching vdo will be of no use, if cant practise