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

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!

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!

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.

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.