Determine active courses in a specific room based on time and date

Courses
1

I need help with a SQL query to find out how many courses are currently happening in a given room. Here’s what I’ve tried:

SELECT COUNT(course_schedule.id) AS active_courses
FROM course_schedule
WHERE
    course_schedule.room_id = @room AND
    course_schedule.event_date = @date AND 
    (
        (course_schedule.begin_time < @start AND course_schedule.finish_time > @end) OR
        (course_schedule.begin_time > @start AND course_schedule.finish_time < @end) OR
        (course_schedule.begin_time < @end) 
    );

My table is called course_schedule and has columns for id, room_id, event_date, begin_time, and finish_time. I want to count courses that overlap with a given time range on a specific date in a particular room. Am I on the right track? How can I make sure this query catches all possible overlapping scenarios?

2

Your approach is on the right track, but I’d suggest simplifying the time overlap logic. Here’s a more straightforward query that should capture all overlapping scenarios:

SELECT COUNT(*) AS active_courses
FROM course_schedule
WHERE room_id = @room
  AND event_date = @date
  AND begin_time < @end
  AND finish_time > @start;

This query checks if the course’s start time is before the end of your time range and its end time is after the start. It’s more efficient and easier to understand.

One thing to consider: Are you handling courses that span multiple days? If so, you might need to adjust the date comparison logic. Also, ensure your time parameters (@start and @end) are in a compatible format with your database’s time fields.

3

hey jack, ur query’s pretty close! instead of the complex OR statements, try this:

SELECT COUNT(*) AS active_courses
FROM course_schedule
WHERE room_id = @room
AND event_date = @date
AND begin_time < @end
AND finish_time > @start;

this should catch all overlaps. btw, how r u handling courses that go past midnight? thats always tricky!

4

Hey Jack27! :wave: Your query looks pretty close, but I think we can tweak it a bit to catch all those tricky overlapping scenarios. Have you considered using BETWEEN for the time comparisons? It might make things cleaner.

Also, I’m curious - are you dealing with any edge cases, like courses that run past midnight? Those can be a real headache! :sweat_smile:

What about something like this:

SELECT COUNT(*) AS active_courses
FROM course_schedule
WHERE room_id = @room
  AND event_date = @date
  AND @start < finish_time
  AND @end > begin_time;

This should catch any course that overlaps with your time range. What do you think? Does this fit your use case better?

Oh, and just wondering - how are you handling timezones in your database? That’s always a fun challenge with scheduling stuff!