Check if data range overlaps

[micang]

Hi All,

I have a table with circa 720,000 unique member numbers, I have a second table with multiple member numbers which has spans for each member. Member spans are defined by a date range (a start member date and an end member date). Each member can have any number of spans. These spans cannot overlap i.e. the same date cannot be in the 2 or more spans. There is no time involved in these dates, its on full day. Teh combination of members and spans results in around 3.2 million records.

My question is I would like to identify records that has overlapping data.

Below are two examples of a scenario - the first example shows that the data is OK, no overlapping, the second example shows that there is an overlap - 2 end dates of "2001-09-30".

Also, the data is not sequential, a member may go for days or months without membership, so there are gaps.

Example 1
MemberNo YMDStart YMDEnd
123 1997-06-01 1997-09-30
123 1997-10-01 1997-10-31
123 1997-11-01 1997-12-31
123 1998-01-01 1998-12-31
123 1999-01-01 1999-06-30

Example 2
MemberNo YMDStart YMDEnd
417 2001-06-01 2001-09-30
417 2001-10-01 2001-09-30
417 2001-11-01 2001-12-31

I have had a search online and am rather confused at to where to begin, in theory it didn't seem too complicated, but when starting to try for a solution, it seems it's pretty complicated.

If someone could start me in the correct direction, it will be appreciated.

Thanks
Michael

 

[gmmastros]

There will likely be different ways to accomplish this. If anyone presents a solution, you should run all, and use whichever returns the correct value and does it in the shortest amount of time.

I would approach this by assigning a row number to each row (similar to a unique identifier), then join the table to itself so that I can compare the ranges.

Something like this...

; With Data As
(
  Select  *,
          Row_Number() Over (Partition By MemberNo Order By YMDStart) As RowId
  From	  YourTableNameHere
)
Select  *
From    Data As A
        Inner Join Data As B
          ON A.MemberNo = B.MemberNo
          And A.RowId = B.RowId - 1
Where   B.YMDStart Between A.YMDStart And A.YMDEnd
        Or
        B.YMDEnd  Between A.YMDStart And A.YMDEnd

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[micang]

Thank you gmmastros, this has indeed identified the records that are overlapping, appreciate it.

Thanks
Michael