BaldyWeb

Overlapping records

In an application that does reservation bookings or perhaps employee sick and vacation tracking, you often need to find records that overlap a given date range.  In other words, find all the employees that were sick or on vacation during a given month, and where any portion of that time off overlaps the period.  The various possibilities are illustrated below:

Desired range
Start Date   End Date
| |
|<------------------->| | | Begins and ends before range - we don't want
| |
|<--- -------|------- -->| | Begins before, ends during - we want this one
| |
| |<---------->| | Begins and ends during - we want this one too
| |
| |<--- -------|------- -->| Begins during and ends after - we also want this one
| |
| | |<------------------->| Begins and ends after - we don't want this one
| |
|<--- -------|------- --------------- -------|------- -->| Begins before and ends after - we want this one
| |

The technique involves testing the given start date against the end date in the table, and the given end date against the start date in the table.  It sounds counter-intuitive at first to compare start to end and end to start, but it works.  So, given a table with drivers and the start and end dates they are off, and a form with a date range, the following SQL will find anyone whose vacation overlaps the date range:

SELECT Driver, OffStartDate, OffEndDate, Reason
FROM tblSickVac
WHERE OffStartDate <= Forms!FormName.txtEndDate And OffEndDate >= Forms!FormName.txtStartDate

Home