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.