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 |
SQL >