I’m selecting conference timeslots for parent/teacher conferences. When a parent is also a teacher and is selecting a timeslot to sign up with another teacher, I want to exclude any timeslots that are booked for the parent as a teacher. For example, ParentTeacher Smith, a high school teacher, has high school conferences starting at 8:00am to 3:00pm and they are 20 minutes apart. That is 8:00, 8:20, 8:40, 9:00, etc. Smith has a student in the middle school and the conferences there are 15 minutes apart starting at 8:00am to 3:00pm. That is 8:00, 8:15, 8:30, 8:45, 9:00, etc. Now Smith wants to book a conference with MiddleSchool Jones. Currently Smith only has one conference at the high school and it’s at 8:40-9:00. When Smith wants to book with Jones there is a conflict at 8:30 and 8:45 with Jones. Over time there will be many more conflicts as parents sign up with Smith.
I can do a query of Smith’s schedule and have a result set that contains booked timeslots; 8:40-9:00, etc. and a result set of Jones’ open timeslots but I don’t want it to include the 8:30-8:45 or the 8:45-9:00 timeslots. The brute force approach I am considering is using a script to loop through the Jones open timeslots, check each one against the Smith booked timeslots and just not display the conflicting timeslots. This seems inelegant and time consuming. Is there a query that will let me select only the timeslots that don’t overlap the booked timeslots? Something maybe with a subquery of the booked timeslots?
Smith (236) Booked
SELECT intSlotID, dtSlotStart, dtSlotEnd
FROM tblSchedules
WHERE intTeacherID = 236 AND intParentID <> 0 AND intSchoolID = 1
Jones (343) Open
SELECT intSlotID, dtSlotStart, dtSlotEnd
FROM tblSchedules
WHERE intTeacherID = 343 AND intParentID = 0 AND intSchoolID = 2