SQL Query | How to identify Overlapping Date Ranges

[@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

[@ravibrussels]

Thanks. it helped me a lot in my job.

[@LearnatKnowstar]

Glad it helped! Thank you so much!

[@surajpatil3579]

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

[@LearnatKnowstar]

You need to apply filters on the source table in that case. For ex executing the query for 1 years data at a time

[@surajpatil3579]

@@LearnatKnowstar Thanks again

[@alexander.kochkarev]

Cool! Smart solution!

[@LearnatKnowstar]

Thank you

[@Mustaq15]

Love the way you explain.❤

[@LearnatKnowstar]

Thank you 🙏

[@yoyoyoyoll]

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

[@LearnatKnowstar]

Glad it was helpful!

[@imranpathan8352]

Provide database..nice video

[@M_S_O_Influence_Gaming]

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

[@Tabla_Tejas67]

Thank You SOOOO Much Mam

[@LearnatKnowstar]

Hope it was helpful

[@nikolaybaranov2213]

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

[@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

[@MK-lh3xd]

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.

[@theraizadatalks14]

You're correct bud!

[@shreyashchoudhary6827]

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