Preventing overlapping appointment times

Question

Hello All,

I have a confusing little problem in SQL-Server 2005 and Classic ASP. I have the following table in the database

+-----------+----------+----------+--------------+-------------------------+-------------------------+--------------+
| ProgramID | SystemID | ClientID | ProgramName  | ProgramStart            | ProgramEnd              | ProgramHours |
+-----------+----------+----------+--------------+-------------------------+-------------------------+--------------+
| 22        | 18       | 4        | After Gym    | 1900-01-01 09:00:00.000 | 1900-01-01 11:00:00.000 | 2hrs 0mins   |
| 23        | 18       | 4        | Free Weights | 1900-01-01 12:00:00.000 | 1900-01-01 14:00:00.000 | 2hrs 0mins   |
+-----------+----------+----------+--------------+-------------------------+-------------------------+--------------+

This basically shows a program that is assigned to a system and a client for a specific time period. What I want to do is prevent a user from entering a third program that overlaps the times already used (e.g. 10am to 11am). The creation page features drop menus for hours, minutes and am/pm for the start hour and the 3 more drop menus for the end hour. What I want to do is fire an ajax script that looks for overlapping entries when the drop menu selection changes. If it finds one it fires a js alert.

How would you detect this overlap in SQL?

Any ideas would be greatly received.

Many Thanks, Paul

Answer 1

You can do a select to see if date entered falls in any ranges:

SELECT COUNT(1) as tCount FROM TABLE WHERE programStart <= formDate AND programEnd  <= formDate

If it returns tCount as being >=1 then there is an overlap.

Also, your program length shouldn't really be stored in the database, as it is technically repeated data that will need updating everytime you change a program time, I would recommend the value of this can be extracted when needed (using SQL dateDiff() for example).

Answer 2

SELECT * FROM appointments WHERE 
(time_from <= $from and time_to >= $to) or // determine if the new appointment is fully in an exitent
(time_from <= $from and $from < time_to and time_to <= $to) or // determine if the new appointment starts in an existing and ends after
(time_from >= $from and time_to >= $to and $to > time_from) or // determine if the new appointment starts before an existing and ends in
(time_from > $from and time_to < $to) // determine if the new appointment starts before and ends after

if more any records are selected there is an overlapping

edit: i corrected the statement. it should now work.

Answer 3

I finally resolved the problem with the following SQL Statement:

SELECT COUNT(*) AS Program_Count FROM Programs
WHERE ('1900-01-01 10:00:01.000' BETWEEN ProgramStart AND ProgramEnd)
OR ('1900-01-01 10:59:59.000' BETWEEN ProgramStart   AND ProgramEnd)
OR (ProgramStart BETWEEN '1900-01-01 10:00:01.000' AND '1900-01-01 10:59:59.000')
OR (ProgramEnd BETWEEN '1900-01-01 10:00:01.000' AND '1900-01-01 10:59:59.000')
AND SystemID = 18;"

Thank you for all your suggestion which when combined and researched brought me this solution.