[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 512
  • Last Modified:

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
0
RabbitDragon
Asked:
RabbitDragon
  • 2
1 Solution
 
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now