Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trialmykolash
12,955 PointsHave an idea how to improve building schedule
Hi there,
what do you think about improving a bit building schedule example, eh?
-- Generate a schedule for Rex Rios.
SELECT Periods.id AS '#',
Periods.start_time AS "Period starts at",
Periods.duration || " minutes" AS "Period duration",
--, TIME(Periods.start_time, "+ Periods.duration") AS "Period ends at"
Classes.room_id AS "Room",
Subjects.name AS "Subject",
Teachers.last_name AS "Teacher"
FROM Periods
INNER JOIN Classes ON Periods.id = Classes.period_id
INNER JOIN Subjects ON Classes.subject_id = Subjects.id
INNER JOIN Schedule ON Classes.id = Schedule.class_id
INNER JOIN Students ON Schedule.student_id=Students.id
INNER JOIN Teachers ON Classes.teacher_id=Teachers.id
WHERE Students.first_name="Rex" AND Students.last_name="Rios"
ORDER BY Periods.id ASC;
PS: Not sure how to improve column in commented row:
--, TIME(Periods.start_time, "+ Periods.duration") AS "Period ends at"
cause time format seems to be provided in a bit buggy manner - "9:05" instead of "09:05" and "1:15" instead of "13:15" - may it be done in purpose, in order to UPDATE Periods table? And yes, imho, we do need this column in case Rex Rios will count seconds to the end of the too boring period - you know, all we did once! ;-)
2 Answers
Steven Parker
231,184 PointsThat's a nice enhancement. Good job.
And it's a bit of a mess to look at, but this will give you the ending time in the same format as the start time:
LTRIM(STRFTIME('%H:%M',
SUBSTR('0'||Periods.start_time, -5),
'+'||Periods.duration||' minutes',
CASE WHEN
CAST(STRFTIME('%H',
SUBSTR('0'||Periods.start_time, -5),
'+'||Periods.duration||' minutes')
AS INTEGER) > 12
THEN '-12 hours' ELSE '+0 hours' END
), '0') AS "Period ends at"
mykolash
12,955 PointsCool-l-n-magic!
mykolash
12,955 PointsSo, final request seems to look like:
-- Generate a schedule for Rex Rios.
SELECT Periods.id AS '#',
Periods.start_time AS "Period starts at",
Periods.duration || " minutes" AS "Period duration",
LTRIM(STRFTIME('%H:%M',
SUBSTR('0'||Periods.start_time, -5),
'+'||Periods.duration||' minutes',
CASE WHEN
CAST(STRFTIME('%H',
SUBSTR('0'||Periods.start_time, -5),
'+'||Periods.duration||' minutes')
AS INTEGER) > 12
THEN '-12 hours' ELSE '+0 hours' END
), '0') AS "Period ends at",
Classes.room_id AS "Room",
Subjects.name AS "Subject",
Teachers.last_name AS "Teacher"
FROM Periods
INNER JOIN Classes ON Periods.id = Classes.period_id
INNER JOIN Subjects ON Classes.subject_id = Subjects.id
INNER JOIN Schedule ON Classes.id = Schedule.class_id
INNER JOIN Students ON Schedule.student_id=Students.id
INNER JOIN Teachers ON Classes.teacher_id=Teachers.id
WHERE Students.first_name="Rex" AND Students.last_name="Rios"
ORDER BY Periods.id ASC;