MySQL Querying database for time periods not already assigned
العربية
български
català
中文
čeština
dansk
Nederlands
eesti
suomi
français
Deutsch
Ελληνικά
עברית
हिंदी
magyar
Bahasa Indonesia
italiano
日本語
한국어
latviešu
lietuvių
norsk
polski
Português
română
русский
slovenčina
slovenski
español
svenska
ไทย
Türkçe
українська
Tiếng Việt
I have a rather complex-seeming query that will form the basis for an online classroom scheduling tool. My challenge is to develop a method to identify which classes a user is signed up for in the st_schedule table, then deduce from the overall table of classes, st_classes, which other classes are available that don't conflict with the user's current classes.
For example, if a user has an entry in st_schedule assigning them to a class from 8:00am to 9:00am, they would be ineligible for any class whose time fell between 8:00am and 9:00am. A class that ran 7:15am - 8:15am would make the user ineligible. I store the start times and end times of classes in the database separately for comparison purposes. It's important that this be as flexible as possible, so the concept of "blocking" times and assigning times to blocks is not a possibility.
Here are excerpts from the tables:
table st_classes (holds class information)
id
start_time
end_time
table st_schedule (holds schedule information)
id
user_id
class_id
I certainly could do this in a series of loops server-side, but I have to think that there's a MySQL method that can do this type of operation in one fell swoop.
Answer |
This is using table variables in mssql but the sql selects should translate over to mysql
First the sample data
DECLARE @st_classes TABLE
(
ID INT NOT NULL,
Title VARCHAR(40) NOT NULL,
StartTime DATETIME NOT NULL,
EndTime DATETIME NOT NULL
)
DECLARE @st_schedule TABLE
(
ID INT NOT NULL,
UserID INT NOT NULL,
ClassID INT NOT NULL
)
INSERT INTO @st_classes (ID, Title, StartTime, EndTime)
SELECT 1,'Class1','08:00:00','09:30:00' UNION
SELECT 2,'Class2','09:30:00','11:30:00' UNION
SELECT 3,'Class3','11:30:00','16:00:00' UNION
SELECT 4,'Class4','16:00:00','17:30:00' UNION
SELECT 5,'Class5','09:00:00','11:45:00' UNION
SELECT 6,'Class6','07:00:00','18:00:00'
INSERT INTO @st_schedule(ID, UserID, ClassID)
SELECT 1,1,1 UNION
SELECT 2,1,2 UNION
SELECT 3,2,6
Next a bit of sql to confirm the tables join OK (selecting scheduled courses for user with an ID of 1) - Returns class 1 and 2
SELECT *
FROM @st_schedule AS S INNER JOIN
@st_classes AS C ON S.ClassID = C.ID
WHERE S.UserID = 1
Now we need to select all the ID of the courses where they overlap time wise with the users scheduled ones (including the scheduled ones) - Returns 1,2,5,6
SELECT AC.ID
FROM @st_classes AS AC
INNER JOIN ( SELECT C.StartTime,
C.EndTime
FROM @st_schedule AS S
INNER JOIN @st_classes AS C ON S.ClassID = C.ID
WHERE S.UserID = 1
) AS UC ON ( AC.StartTime < DATEADD(ss, -1, UC.EndTime)
AND DATEADD(ss, -1, UC.EndTime) > UC.StartTime
)
GROUP BY AC.ID
Now we need to select all courses where the Course ID is not in our list of overlapping course IDs. - Returns course 3 and 4
SELECT *
FROM @st_classes
WHERE ID NOT IN (
SELECT AC.ID
FROM @st_classes AS AC
INNER JOIN ( SELECT C.StartTime,
C.EndTime
FROM @st_schedule AS S
INNER JOIN @st_classes AS C ON S.ClassID = C.ID
WHERE S.UserID = 1
) AS UC ON ( AC.StartTime < DATEADD(ss, -1, UC.EndTime)
AND DATEADD(ss, -1, UC.EndTime) > UC.StartTime
)
GROUP BY AC.ID )
Change the user ID filter to 2 and you should not get any returned as the course assigned to that user overlaps all courses.