2007-11Nov-27

Date range conflicts

One common problem in database applications is checking for conflicts in a date or datetime range. For instance, your application might manage appointments which have a start date and duration. We might talk about minutes or days here, the fundamental problem remains the same. When you enter a new appointment you have to make sure that it doesn't conflict with an existing one.

The problem with date ranges is that there are four variations you have to cover. (a) One range might have no conflicts with another range, (b) might completely fall into the other range, (c) start before, but ends within the other range, (d) starts within, but ends after the other range. I can never remember the query to check for any conflicting items. So here it is (based on http://ryanfarley.com/blog/archive/2004/08/19/966.aspx):

If none of the dates can be empty:

(NOT((table.date_end < m.date_start) ;
or (table.date_start > m.date_end)))

If the end date in the table can be empty to mean a one day appointment, the code would look like this:

(NOT ((table.date_end < m.date_start) ;
OR (table.date_start > m.date_end))) ;
OR (table.date_end=={} AND ;
BETWEEN(table.date_start,m.date_start,m.date_end))

This expression can be used in a SET FILTER TO statement or a WHERE clause. If you need to filter on additional criteria (resource, etc.), you can add those filters with AND. The expression is Rushmore optimizable when you have an index on date_end and date_start in the table.