Skip to content
Advertisement

Find out available days and slots for doctor appointment

I have three tables:

TimeSlot table having(TimeSlotId,TimeSlotStart,TimeSlotEnd,TimeSlotToken)
AppointmentSchedule having (ApptId,DoctorId,TimeSlot1Id,TimeSlot2Id,TimeSlot3Id)
Booking having (BookingId,DoctorId,PatientId,BookingDate,TimeSlotId)

TimeSlotToken is the number of patients a doctor will see in his TimeSlot.
TimeSlot 1, 2, and 3 are Morning, Afternoon and Evening respectively.

I need to find the days where the TimeSlot is not full, i.e TimeSlot token is less than total bookings on that particular day and that slot. Since each doctor may have more than one time slot per day, I am having trouble. If all three TimeSlots are filled in a particular day, show me those unavailable dates (which I will use later). And find the TimeSlots and Dates of those days where timeslots are not filled.

To find the unavailable dates I am trying the code:

with total_token(token) as (
  select timeslottoken 
    from timeslot 
   where timeslotid=(select timeslot1id,timeslot2id,timeslot3id 
                       from appointmentbooking 
                      where doctor id=1001),
with token_as_day(bookingdate,token) as (
  select bookingdate,count(distinct bookingid) 
    from bookings 
   where doctor_id=1001 
   group by booking_date,timeslot_id)   
select token_as_day.bookingdate 
  from total_token,token_as_day
 where token_as_day.token'<'total_token.token;

But this is not working. Please help!
P.S- Column names are not case sensitive. I have more complex names which I simplified here hence the difference..

Advertisement

Answer

Taking from comment to an answer for reference – as OP arrived at the same solution herself.

WITH
Total_Token (timeSlotStart, token) AS (
  SELECT
    timeSlotStart, timeSlotToken
  FROM TimeSlot
  WHERE
    timeSlotId IN (SELECT
                    timeSlot1Id
                  FROM AppointmentSchedule
                  WHERE doctorId = 1
                  UNION ALL
                  SELECT
                    timeSlot2Id
                  FROM AppointmentSchedule
                  WHERE doctorId = 1
                  UNION ALL
                  SELECT
                    timeSlot3Id
                  FROM AppointmentSchedule
                  WHERE doctorId = 1
                  )
),
Token_As_Day (bookingDate, token) AS (
  SELECT
    bookingDate, COUNT(DISTINCT bookingId)
   FROM Booking
   WHERE doctorId = 1
   GROUP BY bookingDate, timeSlotId
)
SELECT
  Token_As_Day.bookingDate
FROM Total_Token
JOIN Token_As_Day
  ON TRUNC(Total_Token.timeSlotStart) = TRUNC(Token_As_Day.bookingDate)
  AND Token_As_Day.token < Total_Token.token
;

See it in action: SQL Fiddle.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement