TSQL - Campus - Student Schedule with Room names by period
Post date: Sep 21, 2017 11:7:31 AM
SQL
-- All Students + Room name per PERIOD
DECLARE @districtID AS INT
SET @districtID=1 -- District ID
DECLARE @schoolIDs TABLE (id INT);
INSERT @schoolIDs(id) VALUES (111111);
SELECT
student_number,
MAX(student_firstname) AS first_name,
MAX(student_lastname) AS last_name,
MAX(CASE WHEN period_name = '1' THEN room_name ELSE NULL END) AS [1],
MAX(CASE WHEN period_name = '2' THEN room_name ELSE NULL END) AS [2],
MAX(CASE WHEN period_name = '3' THEN room_name ELSE NULL END) AS [3],
MAX(CASE WHEN period_name = '4' THEN room_name ELSE NULL END) AS [4],
MAX(CASE WHEN period_name = '5' THEN room_name ELSE NULL END) AS [5],
MAX(CASE WHEN period_name = '6' THEN room_name ELSE NULL END) AS [6],
MAX(CASE WHEN period_name = '7' THEN room_name ELSE NULL END) AS [7],
MAX(CASE WHEN period_name = '8' THEN room_name ELSE NULL END) AS [8],
MAX(CASE WHEN period_name = '9' THEN room_name ELSE NULL END) AS [9],
MAX(CASE WHEN period_name = '10' THEN room_name ELSE NULL END) AS [10],
MAX(CASE WHEN period_name = '11' THEN room_name ELSE NULL END) AS [11]
FROM
(
SELECT DISTINCT
dis.number district_number,
sch.number school_number,
sch.name as school_name,
-- CONVERT(date,te.startDate) term_start,
-- CONVERT(date,te.endDate) term_end,
-- te.name term_name,
cou.courseID course_id,
cou.number course_number,
cou.name course_name,
cou.stateCode course_statecode,
sec.sectionID section_id,
sec.number section_number,
sec.teacherPersonID section_teacher_id,
sec.teacherDisplay section_teacher,
sec.roomID section_room,
sec.sectionID sectionID,
--tri.trialID trialID,
--te.termID termID,
pe.name period_name,
pe.seq period_number,
id.firstName student_firstname,
id.lastName student_lastname,
per.studentNumber student_number,
per.personID persion_id,
roo.name room_name
/*
per.studentNumber,
id.firstName,
id.lastName,
pe.name,
roo.name
*/
FROM
[berea].[dbo].[District] dis
INNER JOIN
[berea].[dbo].[Calendar] cal ON
dis.districtID=cal.districtID
AND cal.endYear =
CASE
WHEN MONTH(GETDATE()) >= 7 THEN YEAR(GETDATE())+1
ELSE YEAR(GETDATE())
END
INNER JOIN
[berea].[dbo].[Course] cou ON
cou.calendarID=cal.calendarID
AND
cou.active = 1
INNER JOIN
[berea].[dbo].[School] sch ON
sch.schoolID = cal.schoolID
AND
sch.number IN (SELECT id FROM @schoolIDs)
INNER JOIN
[berea].[dbo].[ScheduleStructure] scst ON
scst.calendarID=cal.calendarID
INNER JOIN
[berea].[dbo].[Trial] tri ON
tri.calendarID = cal.calendarID
AND
tri.structureID=scst.structureID
AND
tri.active = 1
INNER JOIN
[berea].[dbo].[TermSchedule] tesc ON
tesc.structureID=tri.structureID
INNER JOIN
[berea].[dbo].[Term] te ON
te.termScheduleID=tesc.termScheduleID
AND
te.endDate >= CAST(YEAR(GETDATE()) AS varchar(4)) + '-12-31'
INNER JOIN
[berea].[dbo].[Section] sec ON
sec.trialID=tri.trialID
AND
sec.courseID=cou.courseID
INNER JOIN
[berea].[dbo].[Roster] ro ON
ro.sectionID = sec.sectionID
AND
ro.trialID = tri.trialID
AND
ro.endDate IS NULL
INNER JOIN
[berea].[dbo].[SectionPlacement] sepl ON
sepl.sectionID=sec.sectionID
AND
sepl.trialID=tri.trialID
AND
sepl.termID=te.termID
INNER JOIN
[berea].[dbo].[PeriodSchedule] pesc ON
pesc.structureID=tri.structureID
INNER JOIN
[berea].[dbo].[Period] pe ON
pe.periodID=sepl.periodID
AND
pe.periodScheduleID=pesc.periodScheduleID
INNER JOIN
[berea].[dbo].[Room] roo ON
roo.schoolID = sch.schoolID
AND
roo.roomID = sec.roomID
INNER JOIN
[berea].[dbo].[Identity] id ON
id.personID=ro.personID
INNER JOIN
[berea].[dbo].[Person] per ON
per.personID = ro.personID
WHERE
dis.districtID = @districtID
) AS thesource
GROUP BY
student_number
ORDER BY
student_number