Background info: I am creating a calendar using the APEX 5.0 calendar object as a tool for a community college to schedule classes. This calendar will have filters for campus, building, and room. When a class is scheduled in a particular room, it needs to be scheduled at a particular time on specific days of the week, throughout the semester. So for example on Tuesdays and Thursdays from the beginning of the semester to the end of the semester. This is working so far.
I have a requirement to create an availability search; for example what rooms have openings at what times during a semester? Since the table that the calendar is based on only contains scheduled events, this creates a problem.
I have come up with two solutions:
1: Create a second "time slot" table, that has 1/2 hour time slots by campus/building/room/day, and then do an outer join to that from my calendar table to find openings. The problem with this approach is that the time slot table is getting to be huge and searches are very slow.
2: Alternative approach: do a self join to the calendar table and then create a function that counts the hours between a scheduled event and the next event. This is a lot leaner and faster, but there are some glitches, for example it doesn't recognize when a new day has started, so if an event ends at 6:00 p.m. on one day and the next event starts at 6:00 a.m. the next day, it will count that as 12 hours being available. That is not what we want to display to the user. We want to display from 6:00 p.m. to then end of the academic day (10 p.m.) so just 4 hours that are actually available.
Does anyone know of a way to search for availability in the APEX 5.0 calendar, other than just clicking through the calendar? Thanks in advance.
For reference, below is the SQL from my function.
select distinct
round( (
(SELECT min(Ws.EVENT_start_TIME)
FROM calendar_events Ws
WHERE 1=1
AND Ws.EVENT_START_TIME > p_StartTime
AND SSRMEET_BLDG_CODE = P_BUILDING
AND SSRMEET_ROOM_CODE = P_ROOM
) -
(SELECT max(We.EVENT_end_TIME)
FROM calendar_events We
WHERE 1=1
AND We.EVENT_START_TIME = p_StartTime
AND SSRMEET_BLDG_CODE = P_BUILDING
AND SSRMEET_ROOM_CODE = P_ROOM
)
) * 24, 2)
INTO v_GapInHours
from calendar_events W1
where 1=1
--parameters
and SSRMEET_TERM_CODE = p_Term
and SLBBLDG_CAMP_CODE = p_Campus
and SSRMEET_BLDG_CODE = p_Building
and SSRMEET_ROOM_CODE = p_Room
and event = p_Event
and event_start_time = p_StartTime;