#StackBounty: #mysql #query Scheduling – First or Next Available Appointment with MySQL

Bounty: 100

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.

  1. 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.

  1. 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


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.