1

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
Ed Bangga
  • 11,622
  • 4
  • 10
  • 29
Kibbage
  • 13
  • 4
  • 3
    please provide DDL, sample data, and your expected result, preferably using DB Fiddle or similar so we don't have to guess what you are after. – SQLRaptor Aug 12 '19 at 23:40
  • How about something like (pseudo query): select * from available_slots where start_time not in (select * from booked_parent_slots where parent_id = teacher_id) – Bohemian Aug 12 '19 at 23:45
  • Allocation is hard to do in SQL, especially when the allocated entries interfere. Maybe try solving a SUDOKU in SQL first? – wildplasser Aug 12 '19 at 23:54
  • Are your times stored in a TIME datatype? If so, then you should be able to use the database engine's understanding of time to your advantage. In words: select all Slots from Jones schedule, where Smith does not have a start (or end) time within that time range... SELECT intSlotID from Jones_tblSchedules WHERE Smith_dtSlotStart NOT BETWEEN Jones_tblSchedules.dtSlotStart AND Jones_tblSchedules.dtSlotEnd – mgrollins Aug 13 '19 at 00:01
  • [This question](https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) will show you how to compare two ranges for overlap. Then you just need to find all slots (open for Smith, booked for Jones) that don't overlap each other. – Nick Aug 13 '19 at 01:12
  • @mgrollins - Just needed to include an dtSlotEnd check as Julian did below. Otherwise you are correct. – Kibbage Aug 13 '19 at 05:00

1 Answers1

0

Based on my understanding of your issue this is a solution working on Oracle database. I am sure it will not take you long to convert in your specific database

Let's create the schedule database and populate it with Jones and Smith slots:

CREATE TABLE schedule (teacher_id number, slot_start number, slot_end number, booked char(1));

INSERT INTO schedule(teacher_id, slot_start, slot_end, booked)
SELECT 343 AS teacher_id, 8 * 60 + (level -1) * 15 AS slot_start, 8 * 60 + level * 15 AS slot_end, 'N' as booked
FROM dual connect by Level < 7 * 4 + 1;

INSERT INTO schedule(teacher_id, slot_start, slot_end, booked)
SELECT 236 AS teacher_id, 8 * 60 + (level -1) * 20 AS slot_start, 8 * 60 + level * 20 AS slot_end, 'N' as booked
FROM dual connect by Level < 7 * 3 + 1;

Please note I expressed the slot start and end times as the number of minutes since the mid night. You can adapt for more multi days schedulers to have the number of minutes since the start of the year or whatever else suits you to model your business.

Now let's book Smith's 8:40 - 9:00 slot..Again expressed in minutes.

update schedule set booked = 'Y' WHERE teacher_id = 236 AND slot_start = 60 * 8 +40;

And here is your query selecting available Jones slots:

SELECT teacher_id,
       trunc(slot_start/60) || ':' || RPAD(to_char(MOD(slot_start, 60)), 2, '0') as slot_start,
       trunc(slot_end/60) || ':' || RPAD(to_char(MOD(slot_end, 60)), 2, '0') as slot_end
 FROM schedule jones
 WHERE teacher_id = 343
   AND jones.booked = 'N'
 AND NOT EXISTS (
   SELECT 1
     FROM schedule smith
    WHERE smith.teacher_id = 236
      AND smith.booked = 'Y'
      AND ((smith.slot_start BETWEEN jones.slot_start AND jones.slot_end)
            OR
            (smith.slot_end BETWEEN jones.slot_start AND jones.slot_end)
          )
 )
ORDER BY jones.slot_start;

I done minutes to hour conversion to make the result easier to read:

As you can see 8:30 to 8:45 and 8:45 to 9:00 slots are not selected.

teacher_id slot_start   slot_end
343         8:00            8:15
343         8:15            8:30
343         9:15            9:30
343         10:15           10:30
.....

Now lets book another slot for Smith between 9:40 and 10:00. and run the same select again

update schedule set booked = 'Y' WHERE teacher_id = 236 AND slot_start = 60 * 9 +40;

As you can see from the result the (:30 to 9:45 and 9:45 to 10:00 slots were also exluded

Julian
  • 2,597
  • 5
  • 29
  • 58