Date range conflicts
2007-11Nov-27
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.
If none of the dates can be empty:
If the end date in the table can be empty to mean a one day appointment, the code would look like this:
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.
(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.