1

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

2
  • Silly question, but are you just scrounging around Stack Exchange sites looking for "free code"? Given the three expectations, there is an insufficient amount of source data to work with. You may want to instead build something — even if it's incomplete — and come back with examples of what has been done, what doesn't work, and a richer dataset to allow for better testing of conditions 😕
    – matigo
    Sep 5, 2021 at 22:21
  • @matigo Valid comments. I am a Java developer and I have this logic in Java and trying to move this to SQL engine. Here is the fiddle link, Fiddle Link:- sqlfiddle.com/#!9/b48bf8/3 Sep 7, 2021 at 0:48

2 Answers 2

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.