Weekly (Course) Schedule for Students
-
I have a table that includes student's registered courses with day (1-6), start and end hours (9-21).
Basic query is like that:
SELECT courseName, courseDay, courseStartHour, coursEndHour
FROM courses WHERE studentId = 1
I want to show weekly schedule for a student like that:
This is enough hard for me but there is a harder part. Two courses can conflict.
So Monday 10-11 can include CourseName3. Are these possible with just SQL?
Sample Data:
CREATE TABLE StudentCourses ( courseCode varchar2(8) NOT NULL, courseName varchar2(64) NOT NULL, day number(10), startHour number(10), endHour number(10));
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 352','Advertising Copywriting','1','9','11' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 352','Advertising Copywriting','1','11','13' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 332','Positioning Strategy in Advertising','2','9','12' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'COMM 324','Persuasion and Perception','2','14', '17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 312','Corporate Communications Practicum','3','14','17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 302','Strategic Media Planning','4','9','11' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 302','Strategic Media Planning','4','11','13' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 412','Case Studies in Advertising','4','13','15' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 411','Advertising Photography','4','14','16' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 412','Case Studies in Advertising','4','15','17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 411','Advertising Photography','4','16','18' );- This topic was modified 4 years, 11 months ago by CryptoGraphic.
- This topic was modified 4 years, 11 months ago by CryptoGraphic.
- This topic was modified 4 years, 11 months ago by CryptoGraphic.
-
Is this definitely SQL Server? There is no
numberdatatype in SQL Server, and neither is there avarchar2. Yes, this can be achieved in SQL Server, but considering this looks more like Oracle or MySQL then you would be better off asking on a website for the RDBMS you are using; the syntax will (likely) be different for each.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
The DDL Looks like Oracle to me. To stay Close with your original code, i replaced the houre "numbers" with "small numbers".
In real world you might want to use the TIME data type, since an end hour "11" woul overlap with a start hour "11" - the demo might look more complicated because I changed These types and substracted one Minute from each end hour (to make 10:59 rather 11:00)
Noticed, my sample code doesn't work perfectly yet 😉
-
I created sample on Oracle but we have lots of Sql Server also. I didn't think it's so important. Sorry.
-
Sorry for the delay. I got stroke by a bad headache that knocked me out for over 24 hours. Your posting is fair enough, as we deal with both Oracle and Microsoft in many companies and the DDL wasn't that hard to translate 😉
Since this is an MSSQL-Forum I just stay with their syntax.
I am using the CTE-Syntax to separate 'technical' work from logic. In that first step (rawData) I simply change the hour-number to a time and subtract one minute from each end to avoid false positives for courses that end and start at the same time (I think you wouldn't want them, but that's definition of course). Next preparation task is just to keep the code readable; I 'calculate' the help fields using window functions (lag/lead) and more casting.
The code phases out collisions with previous and next courses so it should be obvious how it works.
If you want to know more, check out this great paperwork
http://www.kodyaz.com/t-sql/sql-query-for-overlapping-time-periods-on-sql-server.aspx
CREATE TABLE StudentCourses ( courseCode varchar(8) NOT NULL, courseName varchar(64) NOT NULL, day smallint, startHour smallint, endHour smallint);
with rawData as
(
select
*,
cast(cast(startHour as varchar(2)) + ':00' as time) as startTime,
cast(cast(endHour - 1 as varchar(2)) + ':59' as time) as endTime,
lag(cast(cast(startHour as varchar(2)) + ':00' as time), 1) over(partition by [day] order by startHour) as prevStartTime,
lag(cast(cast(endHour - 1 as varchar(2)) + ':59' as time), 1) over(partition by [day] order by startHour) as prevEndTime,
lead(cast(cast(startHour as varchar(2)) + ':00' as time), 1) over(partition by [day] order by startHour) as nextStartTime,
lead(cast(cast(endHour - 1 as varchar(2)) + ':59' as time), 1) over(partition by [day] order by startHour) as nextEndTime
from StudentCourses
)
select
[Day],
courseCode,
startHour,
endHour,
case when
(startTime between prevStartTime and prevEndTime)
or (endTime between prevStartTime and prevEndTime)
or (startTime < prevStartTime and endTime > prevEndTime)
or (startTime > prevStartTime and endTime < prevEndTime)
then 'yes'
else 'no'
end as [overlapping with previous],
case when
(startTime between nextStartTime and nextEndTime)
or (endTime between nextStartTime and nextEndTime)
or (startTime < nextStartTime and endTime > nextEndTime)
or (startTime > nextStartTime and endTime < nextendTime)
then 'yes'
else 'no'
end as [overlapping with next]
from rawData
--where
--[Day] = 4 - CryptoGraphic wrote:
I created sample on Oracle but we have lots of Sql Server also. I didn't think it's so important. Sorry.
Knowing what RDBMS you are using, and asking a question specific to that RDBMS is really important. Although they all do the same job, the way they do it can be quite different. Syntax between the different dialects of SQL can be very different as well, and none of them fully support ISO SQL Standards, nor the same ones; meaning that just because it's ISO SQL, and works on Oracle doesn't mean it'll work on PostgreSQL, SQL Server, MySQL, etc.
You haven't acutally explain your end result set, so I've had to guess you want delimited data? if not, then you'll need to show that, but this should be the starting point you need.
USE Sandbox;
GO
CREATE TABLE StudentCourses (courseCode varchar(8) NOT NULL,
courseName varchar(64) NOT NULL,
day smallint,
startHour tinyint,
endHour tinyint);
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 352','Advertising Copywriting','1','9','11' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 352','Advertising Copywriting','1','11','13' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 332','Positioning Strategy in Advertising','2','9','12' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'COMM 324','Persuasion and Perception','2','14', '17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 312','Corporate Communications Practicum','3','14','17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 302','Strategic Media Planning','4','9','11' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 302','Strategic Media Planning','4','11','13' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 412','Case Studies in Advertising','4','13','15' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 411','Advertising Photography','4','14','16' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 412','Case Studies in Advertising','4','15','17' );
INSERT INTO StudentCourses (courseCode, courseName, day, startHour, endHour ) VALUES ( 'ADV 411','Advertising Photography','4','16','18' );
GO
WITH Tally AS(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL)) N1(N),
(VALUES(NULL),(NULL),(NULL),(NULL),(NULL)) N2(N)),
[Hours] AS(
SELECT I
FROM Tally
WHERE I BETWEEN 8 AND 20),
Courses AS(
SELECT H.I AS [Hour],
D.DayNum,
D.DayName,
STUFF((SELECT ',' + SC.courseName
FROM dbo.StudentCourses SC
WHERE SC.startHour <= H.I
AND SC.EndHour > H.I
AND SC.[day] = D.DayNum
ORDER BY SC.courseCode
FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,1,'') AS Courses
FROM [Hours] H
CROSS APPLY (VALUES(1,'Mon'),(2,'Tue'),(3,'Wed'),(4,'Thu'),(5,'Fri'),(6,'Sat'),(7,'Sun')) D(DayNum, DayName))
SELECT C.Hour,
MAX(CASE C.DayNum WHEN 1 THEN C.Courses END) AS Mon,
MAX(CASE C.DayNum WHEN 2 THEN C.Courses END) AS Tue,
MAX(CASE C.DayNum WHEN 3 THEN C.Courses END) AS Wed,
MAX(CASE C.DayNum WHEN 4 THEN C.Courses END) AS Thu,
MAX(CASE C.DayNum WHEN 5 THEN C.Courses END) AS Fri,
MAX(CASE C.DayNum WHEN 6 THEN C.Courses END) AS Sat,
MAX(CASE C.DayNum WHEN 7 THEN C.Courses END) AS Sun
FROM Courses C
GROUP BY C.[Hour]
ORDER BY C.[Hour];
GO
DROP TABLE dbo.StudentCourses;Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Wow. This is perfect, with empty hours and conflicted hours.
Works perfect on Sql Server. I just couldn't run it on Oracle, it's my fault. Wrong Forum. 🙂
Thank you very much.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply