CODEWITHSUNDEEP
sqldatabasems-accessms-access-2010
Anon
Anon

Reputation: 105

How do I create a query that checks time overlaps?

Say I wanted to write a query that finds all professors that are available at an arbitrary time slot. I have a table of professors and the times each professor is unavailable.

The professor table has an entry for each professor with their name and a time slot that they are busy (so a professor with multiple busy time slots would have multiple entries).

My problem is that if a professor is already busy during a specific time slot x and also busy at another time slot y, it will return that the professor is available for x because y doesn't interfere.

So say I need to find a list of the professors that are available at 12pm. Professor Jones is busy at 12pm and also busy at 2pm. Every query I come up with returns professor Jones as available for the 12pm slot because it sees the entry for 2pm and knows that that doesn't overlap. (The actual query needs to be generic though so it goes through the list of time slots and returns a large table with each time slot and each professor available at that time slot, rather than a SELECT time WHERE time = "12")

Upvotes: 0

Views: 549

Answers (1)

Magdalena Parks
Magdalena Parks

Reputation: 81

Based on your description and comments I'm going to assume you have the following table structure:

professor
- name string
- start datetime
- end datetime

It's unclear based on your description whether the input for the query is a single point in time or a range of time. You then go on to mention that the solution needs to be 'generic' so that it goes through 'the list of time slots'. What is this list? Where are you getting it? For a more thorough answer I'll need you to give me a better description.

For now, though, I'm going to assume that the input for the query is a single datetime value called $checktime, and that you are trying to find the list of professors who are available at that exact moment in time. It should be fairly simple to change that to a range of time instead.

Based on the description of the problem with your current query, it sounds like the current query is something like this:

SELECT DISTINCT name
FROM professor 
WHERE $checktime < start 
    OR $checktime > end

If you had two entries, (Jones, 12pm, 1pm) and (Jones, 2pm, 3pm), and ran that query using $checktime=12pm, the where clause would filter out the first entry, but the second entry would remain, leaving Jones in the result table. This seems to match the description you provided.

The trick here is to instead find the list of all professors who are busy at that time, and then use that list to find the professors who are not busy at that time.

For example, start with the following query, which is basically the opposite of your current query.

SELECT DISTINCT name
FROM professor 
WHERE $checktime >= start
    AND $checktime <= end

This will get you the list of all professors who are busy at the time given. You can then get a list of all professors and use MINUS to remove entries contained in the previous query:

(
-- The list of all professors
SELECT DISTINCT name
FROM professor
)
MINUS
(
-- The list of all professors who are busy at the given time slot.
SELECT DISTINCT name
FROM professor 
WHERE $checktime >= start
    AND $checktime <= end 
)

The remaining professors are all professors who are not busy the the given time.

I'm not familiar with MS Access, but if it happens to be one of the database engines that doesn't support MINUS, you can instead do the following, which is logically equivalent (assuming table professor has constraint on name to not be null), but a lot less readable:

SELECT DISTINCT all_profs.name
FROM professor as all_profs
LEFT JOIN 
    (SELECT DISTINCT name
    FROM professor 
    WHERE $checktime >= start
        AND $checktime <= end
    ) as busy_profs
ON all_profs.name = busy_profs.name
WHERE busy_profs.name IS NULL

As a side note, you should consider changing your table structure so that the busy times are in a table separate from professor, such as a new table called busy_times with 3 columns: professor (foreign key to the professor table), start, and end. This is because, presumably, you have more information on a professor than just their names, and most professors will have many busy times. This results in a lot of repeated information, which is bad for two reasons. First, it takes up a lot more space than it should. Second, it is very cumbersome to maintain. What happens if a professor gets married and changes their name? You would have to change every entry, and if you missed one it could cause problems.

Upvotes: 2

Related Questions