pink cylinders

More than Four

...there's an axis for that.

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

 

Add comment

Loading