SQL‎ > ‎

TSQL - Campus - Student Schedule with Room names by period

posted Sep 21, 2017, 4:07 AM by Chris Franklin   [ updated Sep 21, 2017, 4:08 AM ]
student_numberfirst_namelast_name1234567891011
1234TomAnderson221223101CAFPOLPOLPOLPOLPOLPOLPOL
33434LisaFerguson226233251207176CAFCAF231231232105
1234123HomerHudimacOMBOMBOMBOMBOMBOMBOMBOMBOMBOMBOMB
1234BartMichael231243252107CAF106106CAFWORLDWORLDWORLD
4312BenderBlaise106202WORLDWORLDWORLDWORLDWORLDWORLDWORLDWORLDWORLD
2341FranklinStrongarm221GYM101225CAF252252223223251WORLD
42341BobGoogle273AIDE251CAF176225225106106WORLDWORLD


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

Comments