Oracle 11g – Slice Time Period into 20 Minute Chunks and Group

oracleoracle-11g

I have an Oracle database where I want to create time slots of 20 minutes, and would like to retrieve all the time slots available for a doctor on each clinic based on his shift AND the appointments already scheduled.

Any time slots that are in any way already occupied by an appointment OR go beyond the doctor's shift ending time should not be retrieved.

  • A doctor can work on more than one clinic.
  • The doctor shift can start on one day and finish another day.
  • There may be "broken shifts" (Shifts lasting 08:15 hours for example)

I have two tables like the following:

Doctors timetable:

╔══════════════════════════════════════════════════════════════════════╗
║                           CLINICS_TIMETABLE                          ║
╠═══╦════════════╦═════════════╦═══════════════════╦═══════════════════╣
║ID ║ clinic_ID  ║  DOCTOR_ID  ║     SHIFT_START   ║     SHIFT_END     ║
╠═══╬════════════╬═════════════╬═══════════════════╬═══════════════════╣
║ 1 ║     1      ║      1      ║2017-08-03 07:00:00║2017-08-03 12:00:00║
║ 2 ║     1      ║      2      ║2017-08-03 08:00:00║2017-08-03 12:00:00║
║ 3 ║     1      ║      3      ║2017-08-03 08:45:00║2017-08-04 07:15:00║
╠═══╬════════════╬═════════════╬═══════════════════╬═══════════════════╣
║ 4 ║     2      ║      1      ║2017-08-03 18:30:00║2017-08-04 01:00:00║
║ 5 ║     2      ║      2      ║2017-08-03 15:00:00║2017-08-04 02:00:00║
║ 6 ║     3      ║      1      ║2017-08-03 13:00:00║2017-08-03 18:00:00║
╚═══╩════════════╩═════════════╩═══════════════════╩═══════════════════╝

Appointments Table:

╔═══════════════════════════════════════════════════════════════════════════╗
║                             APPOINTMENTS                                  ║
╠═══╦═════════╦═════════╦═══════════╦═══════════════════╦═══════════════════╣
║   ║clinic_id║doctor_id║specialt_id║      start_time   ║     end_time      ║
╠═══╬═════════╬═════════╬═══════════╬═══════════════════╬═══════════════════╣
║ 1 ║    1    ║    3    ║    1003   ║2017-08-03 09:20:00║2017-08-03 09:40:00║
║ 2 ║    1    ║    2    ║    1003   ║2017-08-03 09:20:00║2017-08-03 09:40:00║
║ 3 ║    2    ║    2    ║    1004   ║2017-08-03 15:10:00║2017-08-03 15:30:00║
║ 4 ║    1    ║    3    ║    1003   ║2017-08-03 09:40:00║2017-08-03 10:00:00║
║ 5 ║    1    ║    2    ║    1003   ║2017-08-03 09:50:00║2017-08-03 10:10:00║
║ 6 ║    2    ║    2    ║    1004   ║2017-08-03 15:30:00║2017-08-03 15:50:00║
╠═══╬═════════╬═════════╬═══════════╬═══════════════════╬═══════════════════╣
║ 7 ║    1    ║    2    ║    1005   ║2017-08-03 10:15:00║2017-08-03 10:35:00║
║ 8 ║    3    ║    1    ║    1004   ║2017-08-03 17:20:00║2017-08-03 17:40:00║
║ 9 ║    2    ║    1    ║    1002   ║2017-08-03 19:00:00║2017-08-03 19:20:00║
╚═══╩═════════╩═════════╩═══════════╩═══════════════════╩═══════════════════╝

Doctor 2 expected query result:

╔══════════════════════════════════════════════════╗
║                    TIMESLOTS                     ║
╠═══╦════════════╦═════════════╦═══════════════════╣
║ID ║ clinic_ID  ║  DOCTOR_ID  ║     TIMESLOT      ║
╠═══╬════════════╬═════════════╬═══════════════════╣
║ 1 ║     1      ║      2      ║2017-08-03 08:00:00║
║ 2 ║     1      ║      2      ║2017-08-03 08:20:00║
║ 3 ║     1      ║      2      ║2017-08-03 08:40:00║
║ 4 ║     1      ║      2      ║2017-08-03 09:00:00║
║ 5 ║     1      ║      2      ║2017-08-03 10:40:00║
║ 6 ║     1      ║      2      ║2017-08-03 11:00:00║
╠═══╬════════════╬═════════════╬═══════════════════╣
║ 7 ║     2      ║      2      ║2017-08-03 16:00:00║
║ 8 ║     2      ║      2      ║2017-08-03 16:20:00║
║ 9 ║     2      ║      2      ║2017-08-03 16:40:00║
║ . ║     .      ║      .      ║         .         ║
║ . ║     .      ║      .      ║         .         ║
╚═══╩════════════╩═════════════╩═══════════════════╝

The above table is an abbreviated list of results. For example, there should be rows going from 2017-08-03 11:20 to 2017-08-03 12:00. I took those out for the sake of simplicity.

I know that the number of slots available for each doctor on each shift can be found using the following code snippet:

SELECT ID,((SHIFT_END - SHIFT_START)*60*24/20)number_of_slots 
  FROM CLINICS_TIMETABLE

Where 20 is the length of the slots I want.

Best Answer

One way to approach this problem is by breaking it down into pieces: start with the CLINICS_TIMETABLE table, calculate how many slots fit into a shift, transform each row into the correct number of slots, and join to APPOINTMENTS to remove any slots that overlap with an appointment.

You already have the number of slots calculation in your question:

SELECT
  ct.DOCTOR_ID
, ct.clinic_ID
, ct.SHIFT_START 
, ((SHIFT_END - SHIFT_START)*60*24/20)
FROM CLINICS_TIMETABLE ct;

A convenient way to transform a row into more rows is to join to a numbers table. For this problem I'll put 504 rows into the table which will work as long as a doctor doesn't have a shift that lasts longer than a week:

CREATE TABLE NUMBERS_TBL (
NUM INT,
PRIMARY KEY (NUM)
);

INSERT INTO NUMBERS_TBL
SELECT LEVEL 
FROM DUAL
CONNECT BY LEVEL <= (7 * 1440 / 20)
ORDER BY LEVEL;

Of course, there's nothing wrong with putting more rows into the table.

To get one row per slot per shift I can join to the numbers table as follows:

SELECT
  ct.DOCTOR_ID
, ct.clinic_ID
, ct.SHIFT_START + (nt.NUM - 1 ) * (20 / 1440) SLOT_START
, ct.SHIFT_START + nt.NUM * (20 / 1440) SLOT_END
FROM CLINICS_TIMETABLE ct
INNER JOIN NUMBERS_TBL nt ON nt.NUM <= ((SHIFT_END - SHIFT_START)*60*24/20);

Slots that overlap with an appointment need to be removed from the result set. A row only overlaps if the appointment start time is less than the slot end time and the appointment end time is greater than or equal to the slot start time. We can express this in SQL with an anti-join:

WHERE NOT EXISTS
(
    SELECT 1
    FROM APPOINTMENTS a
    WHERE slots.DOCTOR_ID = a.DOCTOR_ID
    AND a.start_time < slots.SLOT_END
    AND a.end_time >= slots.SLOT_START
)

Putting the full query together:

SELECT slots.clinic_ID
, slots.DOCTOR_ID
, slots.SLOT_START AS TIMESLOT
FROM
(
    SELECT
      ct.DOCTOR_ID
    , ct.clinic_ID
    , ct.SHIFT_START + (nt.NUM - 1 ) * (20 / 1440) SLOT_START
    , ct.SHIFT_START + nt.NUM * (20 / 1440) SLOT_END
    FROM CLINICS_TIMETABLE ct
    INNER JOIN NUMBERS_TBL nt ON nt.NUM <= ((SHIFT_END - SHIFT_START)*60*24/20)
) slots
WHERE NOT EXISTS
(
    SELECT 1
    FROM APPOINTMENTS a
    WHERE slots.DOCTOR_ID = a.DOCTOR_ID
    AND a.start_time < slots.SLOT_END
    AND a.end_time >= slots.SLOT_START
)
ORDER BY DOCTOR_ID, SLOT_START;

I mocked up some of the your data on http://dbfiddle.uk. The rows appear to match the results that you're looking for:

╔═══════════╦═══════════╦═════════════════════╗
║ CLINIC_ID ║ DOCTOR_ID ║      TIMESLOT       ║
╠═══════════╬═══════════╬═════════════════════╣
║         1 ║         2 ║ 2017-08-03 08:00:00 ║
║         1 ║         2 ║ 2017-08-03 08:20:00 ║
║         1 ║         2 ║ 2017-08-03 08:40:00 ║
║         1 ║         2 ║ 2017-08-03 09:00:00 ║
║         1 ║         2 ║ 2017-08-03 10:40:00 ║
║         1 ║         2 ║ 2017-08-03 11:00:00 ║
║         1 ║         2 ║ 2017-08-03 11:20:00 ║
║         1 ║         2 ║ 2017-08-03 11:40:00 ║
║         2 ║         2 ║ 2017-08-03 16:00:00 ║
║         2 ║         2 ║ 2017-08-03 16:20:00 ║
║         2 ║         2 ║ 2017-08-03 16:40:00 ║
║         2 ║         2 ║ 2017-08-03 17:00:00 ║
║         2 ║         2 ║         ...         ║
╚═══════════╩═══════════╩═════════════════════╝
Related Question