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