I have 3 Tables with time slots, doctor's booked schedule, and patients' requests for appointments. I want to write a query to find the number of doctors available at each time slot and also the number of requests for appointments in each time slot.
Slots (10 mins interval)
| Slot_id | slots |
|---|---|
| 1 | 2021-01-01 09:00 |
| 2 | 2021-01-01 09:10 |
| 3 | 2021-01-01 09:20 |
booked_gp_slots (booked timeslots for doctors)
| gp_id | booked_slot_start | booked_slot_end |
|---|---|---|
| 10 | 2021-01-01 09:00 | 2021-01-01 10:00 |
| 10 | 2021-01-01 12:00 | 2021-01-01 12:20 |
| 24 | 2021-01-01 09:00 | 2021-01-01 09:40 |
request_slots (booking request for patients)
| patient_id | req_slot_start | req_slot_end |
|---|---|---|
| 1 | 2021-01-01 09:00 | 2021-01-01 09:30 |
| 3 | 2021-01-01 10:00 | 2021-01-01 10:30 |
| 5 | 2021-01-01 09:00 | 2021-01-01 09:40 |
Sample Output
| Slot_id | slots | GP available | Patient Request |
|---|---|---|---|
| 1 | 2021-01-01 09:00 | 2 | 5 |
| 2 | 2021-01-01 09:10 | 1 | 6 |
| 3 | 2021-01-01 09:20 | 3 | 6 |
Since there is no id to connect the tables I am finding it difficult to tally up the counts for available doctors and appointments. My logic would be to check if the doctor's booked slot start time > Slot time and if that is the case add them up. I am not able to aggregate the column coming from the CTE.
Any advice or help would be appreciated.
with gp_slot as(SELECT
gp_id, booked_slot_start, booked_slot_end
FROM
booked_gp_slots
)
Select
SUM(CASE WHEN
(SELECT gp_slot.booked_slot_start FROM gp_slot) > S.Slots THEN 1 ELSE 0 END) AS GP_availibily
FROM Slots