Detecting overlapping date ranges

Consider an Event table, with columns A and B defining the start and end of the period of time over which the event occurred.
Consider also a random period of time defined from S to E.
Now imagine that you want to select all event rows that overlap that specific period of time.

By overlap, we mean that case 1,2,4, and 6 should be included, but 3 and 5 should not:

image

The first solution that might occur to us is this one:

select * from Event
where B between S and E or A between S and E

At first glance it looks clear and elegant, but unfortunately it doesn’t handle case #6.
Somewhat less intuitive but still elegant and also complete is:

select * from Event
where B >= S and A <= E

Leave a Reply

Your email address will not be published. Required fields are marked *