CODEWITHSUNDEEP
mysqlsql
mike
mike

Reputation: 1338

Comparing date ranges in SQL for specific entries

I have a database as follows:

    ID    |    Term    | Start Date | End Date
    12           6         Jul '12     Dec '12
    12           6         Jan '13     Jun '13
    13           2         Jul '12     Aug '12
    13           12        Jul '12     Jul '13

So each of the ID's is a deal that has been made. This is a database I am dealing with and it looks like some of the data was input incorrectly. I am trying to write a filter wherein overlapping terms for the same deal ID are displayed. So for instance, for ID 12, the contract was for 6 months from Jul - Dec '12, it was then extended for another 6 months. For ID 13, someone input the data incorrectly, the contract was "made twice" in the same term, the dates overlap.

So what I need is a filter that sorts through each collection of ID (there are not necessarily only two, a contract can be extended an arbitrary number of times) and checking for the validity of all the dates within that ID, as defined above. I'm not very SQL native, so while() loops are the first thing that come to mind but I'm wondering if there is an easier way to apply this kind of filter?

Upvotes: 0

Views: 50

Answers (1)

dnoeth
dnoeth

Reputation: 60462

You need to find all rows where the date ranges overlap?

Is there Primary Key/any unique identifier for this table to avoid comparing a row with itself?

select * from vt as t1
where exists
 ( select * from vt as t2
   where t1.id = t2.id  -- same ID
   and t1.PK <> t2.PK   -- but not the same row
   and t1.end_date >= t2.start_date -- date ranges
   and t1.start_date <= t2.end_date -- overlap
 )

Depending on your data you might have to change the comparison to </> instead of <=/>=

Upvotes: 1

Related Questions