Scheduling - First or Next Available Appointment with MySQL

Question

I am looking for first available appointment with all doctors or next available appointment based on the given date.

Tables:-

1. doctor_availability --> This is to store doctors availability for all days in a week.
2. doctor_exception --> This is to store doctors exceptions like lunch hours or so for all days in a week.
3. appointment --> This is to stored the booked appointments.

Table:- doctor_availability

doctor_id	working_day	st_time	end_time
1	1	8:00	17:00
1	2	8:00	17:00
1	3	8:00	15:00
1	4	9:00	16:00
2	1	8:00	17:00
2	5	8:00	17:00
3	2	8:00	15:00
4	4	9:00	16:00

Here, working_day 1 for Sunday and 2 for Monday, ...

Table:- doctor_exception

doctor_id	working_day	st_time	end_time
1	1	12:00	13:00
1	2	12:00	13:00
1	3	13:00	14:00
1	4	13:00	14:00
2	1	12:00	13:00
2	5	12:00	13:00
3	2	12:00	13:00
4	4	12:00	13:00

Appointment:-

appt_id	doctor_id	st_time	end_time
1	1	2021-10-08 09:30	2021-10-08 09:45
2	1	2021-10-08 10:30	2021-10-08 11:00
3	1	2021-10-09 09:30	2021-10-08 09:45
4	2	2021-10-08 09:30	2021-10-08 09:45
5	2	2021-10-09 09:30	2021-10-08 09:45
6	3	2021-10-08 09:30	2021-10-08 09:45
7	4	2021-10-08 09:30	2021-10-08 09:45

1. My requirement is to find the first available appoint with each doctor for the given duration. The duration could be 15 mins or 30 mins or 45 mins based on the Appointment type.

2. It has to return the next available appointment with a given doctor after the given date.

3. Return all free slots by given duration with a given doctor b/w the given time period.

Fiddle Link:- http://sqlfiddle.com/#!9/b48bf8/3

1. Query to get st_time and end_time by adding duration to st_time

SELECT appt_id, st_time, ADDTIME(st_time, duration * 100) AS end_time from apptointment; --> Returns null for appt_id 3. Unable to add 60 mins to start time.

2. Query to get available slots

    SELECT Available_from, Available_to 
        FROM ( SELECT @lasttime_to AS Available_from, 
                      st_time AS Available_to, 
                      @lasttime_to := end_time
               FROM ( SELECT st_time, ADDTIME(st_time,
                            duration * 100) AS end_time
                      FROM apptointment
                      WHERE doctor_id = 1
                        AND st_time >= '2021-09-16 09:00'
                        AND st_time < '2021-09-16 17:00'
                      ORDER BY st_time ) e
               JOIN ( SELECT @lasttime_to := NULL ) init ) x 
        WHERE Available_to >= DATE_ADD(Available_from, INTERVAL 15 MINUTE);
   

This query returns nothing. What I need is to return all available slots by interval.

3. Query to get Available slot b/w given time for a particular doctor.

I am not sure, How to combine the doctor_working_hours and doctor_working_exception

Answer 1

I suggest you split the task in two.

1. The database is the source of truth of what times are taken or available. SELECT the data for the day(s) in question from the table. (This would be approximately the derived table e in your query. It has the day and the doctor, as required.)

2. You have figured out how to do the algorithm in Java. Use that to finish the task.

One of my mantras is "Some tasks are better done in a programming language other than SQL."

I might suggest you use datatypes DATE, DATETIME, and TIME as appropriate in the table. SELECTing will convert them to strings; meanwhile, date arithmetic is readily available in SQL.

ADDTIME(st_time, duration * 100) should probably be st_time + INTERVAL duration MINUTE.

What version are you running? MySQL has 8.0, which has CTEs, which might help. MariaDB has pseudo-sequence tables that greatly help in iterating over all 15-minute periods of a day.

Answer 2

Here is how I would combine your doctor availability into one object. Union allows you to glue to together multiple sets. Keep in mind the number of columns needs to match for each result set. Also... an empty string is a good way to add a column for one query and have blanks for the other set(s). (further reading: https://www.techonthenet.com/mysql/union.php)

Technically here, we are using UNION ALL. The difference is, UNION will filter out duplicates (but with additional overhead). If you're filtering the results properly you should get distinct results

SELECT id, starttime, end_time
FROM
(
SELECT da.doctor_id,  da.st_time as AMStart, de.st_time as AMEnd
FROM  doctor_availability AS da
JOIN doctor_exception AS de on da.doctor_id = de.doctor_id and da.workday = de.workday
WHERE da.doctor_id = 1
GROUP BY da.doctor_id, da.work
UNION ALL
SELECT da.doctor_id,  de.end_time as PMStart, da.end_time as PMEnd
FROM  doctor_availability AS da
JOIN doctor_exception AS de on da.doctor_id = de.doctor_id and da.workday = de.workday
WHERE da.doctor_id = 1
GROUP BY da.doctor_id, da.work
UNION ALL
SELECT apt.doctor_id, apt.st_time, apt_end_time
FROM appointments
WHERE doctor_id <> 1 AND st_time >= @startValue and end_time < @endValue
) as available_appointments

Pseudo Code example to reconcile jagged row lengths:

SELECT da.doctor_id, da.working_day,  de.end_time as PMStart, da.end_time as PMEnd
FROM  doctor_availability AS da
JOIN doctor_exception AS de on da.doctor_id = de.doctor_id and da.workday = de.workday
WHERE da.doctor_id = 1
GROUP BY da.doctor_id, da.work
UNION ALL
SELECT apt.doctor_id, "" as working_day, apt.st_time, apt_end_time
FROM appointments
WHERE doctor_id <> 1 AND st_time >= @startValue and end_time < @endValue