Determine ongoing courses in a specific room given time parameters and date

Courses
1

I’m trying to figure out how many classes are happening in a particular room right now. I’ve got a table called class_schedule with columns for room, date, start time, and end time. Here’s what I’ve come up with so far:

SELECT 
    COUNT(class_schedule.id) AS active_classes
FROM
    class_schedule
WHERE
    class_schedule.room_id = :room AND
    class_schedule.class_date = :today AND 
    (
        (class_schedule.begins < :current_time AND class_schedule.finishes > :current_time) OR
        (class_schedule.begins > :current_time AND class_schedule.finishes < :current_time) OR
        (class_schedule.begins < :current_time) 
    );

Is this the right way to count ongoing classes? I want to make sure I’m catching all possible scenarios where a class might be in session. Any tips on making this more efficient or accurate would be great. Thanks!

2

Hey there Iris_92Paint! :wave:

Your query is on the right track, but I think we can simplify it a bit. Have you considered using BETWEEN for the time check? It might make things clearer. Something like this could work:

SELECT COUNT(*) AS active_classes
FROM class_schedule
WHERE room_id = :room
  AND class_date = :today
  AND :current_time BETWEEN begins AND finishes;

This should catch all classes currently in session. What do you think?

Also, I’m curious - are you building some kind of room management system? Sounds like an interesting project! How many rooms are you dealing with in total?

Oh, and quick thought - have you considered adding a buffer time before and after classes? Sometimes classes run a bit over or start early. Might be worth factoring in if you haven’t already.

Let me know if this helps or if you need any more info!

3

yo iris, your query’s not bad but kinda overkill. try this:

SELECT COUNT(*) AS active_classes
FROM class_schedule
WHERE room_id = :room
  AND class_date = :today
  AND :current_time BETWEEN begins AND finishes;

it’s simpler and works fine. maybe add indexes on room_id and class_date for better speed.

4

Your SQL query is close, but it can be simplified for better efficiency and clarity. Here’s a more streamlined version:

SELECT COUNT(*) AS active_classes
FROM class_schedule
WHERE room_id = :room
  AND class_date = :today
  AND :current_time BETWEEN begins AND finishes;

This approach uses the BETWEEN operator to check if the current time falls within the class timeframe, which is more intuitive and likely performs better.

A couple of additional considerations:

  • Ensure your date and time parameters are in the correct format for comparison.
  • If classes can span midnight, you might need to adjust the logic to account for this.
  • Consider indexing frequently queried columns (room_id, class_date, begins, finishes) for improved performance, especially if you’re dealing with a large dataset.

Hope this helps optimize your query. Let me know if you need any further clarification.