SQL Query to Findout Overlap Dates in Begin and End dates

We have a Table with few Thousands of rows. We are looking to find if there is any overlapping dates for each ID. Here is a example of data.
ID                  From Date                            To Date
51416      2008-11-14 00:00:00.000      2009-06-01 00:00:00.000
51416      2006-04-15 00:00:00.000      2008-11-14 00:00:00.000
51416      2006-02-11 00:00:00.000      2006-04-15 00:00:00.000
51416      2005-05-25 00:00:00.000      2006-02-15 00:00:00.000

If take a look at the last 2 records, The From date is 2006-02-11, where as the previous record has End date as 2006-02-15. In this case the starting date should be 2006-02-15 or later, but should not overlap with previous End date. There should be continuation of dates. If Gaps are there thats fine too.

We are looking to disply those records have Opver Lapping Records.
sqlFrmToDates.jpg
RabbitDragonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JR2003Commented:

SELECT *
  FROM myTable A
 INNER JOIN myTable B
         ON A.Id > B.Id
           AND ((A.FromDate < B.ToDate
                    AND A.ToDate > B.FromDate)
                )
           OR  ((B.FromDate < A.ToDate
                AND B.ToDate > A.FromDate)
               )

Open in new window

0
JR2003Commented:
I now see that you have the same Id for different rows. So this SQL should do the trick:
 

SELECT *
  FROM myTable A
 INNER JOIN myTable B
         ON A.Id = B.Id
        AND A.FromDate < B.ToDate
        AND A.ToDate > B.FromDate
        AND A.FromDate <> B.FromDate
        AND A.ToDate <> B.ToDate

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.