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.