I am looking for first available appointment with all doctors or next available appointment based on the given date.
- doctor_availability --> This is to store doctors availability for all days in a week.
- doctor_exception --> This is to store doctors exceptions like lunch hours or so for all days in a week.
- appointment --> This is to stored the booked appointments.
Here, working_day 1 for Sunday and 2 for Monday, ...
|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|
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.
It has to return the next available appointment with a given doctor after the given date.
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
- 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.
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.
- 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