Prevent Appointment Time Overlap in Insert/Update

[Auguy]

I'm trying to insert/update records for an appointment table and don't want any appointment times to overlap any other for the same salesman. I was going to use integers as the times and only allow 15 minute increments, 8:00 AM would be 800 and 3:30 PM would be 1530. I know I can check this with a select before I issue the update, but thought this method would be safer just in case two users try to add an appointment at the same time. I simplified the code to make it more readable. I will also have to account for an appointment being completely contained with the time frame of another appointment. Is this a good approach? I plan on using the Union instead of an OR to avoid a table scan (I hope). I am trying to avoid a second table with the records for each salesman, date, and 15 minute interval.

Update Appointment
	Set Salesman = @Salesman,
            ApptDate = @ApptDAte,
            StartTime = @StartTime,
	    EndTime = @EndTime

	Where Appointment.PK_Appt = @PK_Appt
	And Not Exists
(
  SELECT 1 
    FROM Appointment 
    WHERE Appointment.Salesman = @Salesman
	And Appointment.ApptDAte = @ApptDAte
        And @StartTime Between Appointment.StartTime And Appointment.EndTime
	And Not Appointment.PK_Appt = @PK_Appt
  Union
  SELECT 1 
    FROM Appointment 
    WHERE Appointment.Salesman = @Salesman
	And Appointment.ApptDAte = @ApptDAte
        And @EndTime Between Appointment.StartTime And Appointment.EndTime
	And Not Appointment.PK_Appt = @PK_Appt
)

Auguy
Sylvania/Toledo Ohio

 

[philhege]

Looks like it would work, but how do you handle adjacent appointments, e.g. the first appointment ends at 1500 and the next appointment begins at 1500?

-----------
With business clients like mine, you'd be better off herding cats.

 

[Auguy]

I'll probably just warn the users, not too many of them or salesman, just trying to do it right. I might add a 15 minute cushion to the start or end of each appt.

Auguy
Sylvania/Toledo Ohio