SQL
TSQL - Campus - EMIS - FT - Student Truancy and Excessive Absence
posted Feb 22, 2018, 7:48 AM by Chris Franklin
TSQL CodeDECLARE @districtID AS INT SET @districtID=999 -- District ID /* 000001 - Junior High School 000002 - Other School 000003 - Elementary 0 000004 - Middle School 000005 - Elementary 1 000006 - Elementary 2 000007 - Elementary 3 000008 - High School */ DECLARE @schoolIDs TABLE (id INT); INSERT @schoolIDs(id) VALUES (024570),(024570),(024521),(003806),(003848),(043612),(014242),(034710),(000005),(011908) /* Create a TMP table with a virtual rowid column */ ;WITH logs AS ( SELECT [personID], [districtID], [dateTimeStamp], [Text], [module], [contactMode], rowid=ROW_NUMBER() OVER ( PARTITION BY personID, districtID, module ORDER BY [personID] DESC, [districtID] DESC, [dateTimeStamp] DESC ) FROM [districtname].[dbo].[ContactLog] conlog WHERE conlog.module = 'PLP' AND conlog.contactMode IN ('Attendance Letter','AIP Implementation Meeting') ) SELECT --DISTINCT right(' ',8), -- filler right(' '+'FT',2), -- 2 right(' ',1), -- filler right(' '+scyr.endYear,4), -- 4d year right(' ',1), --Reporting Period right(' '+dis.number,6), -- district IRN right(' '+per.studentNumber,9), -- student id right(' '+CONVERT(CHAR(8), log.dateTimeStamp, 112),8), -- event date CCYYMMDD log.text --.per.personID --.right(' '+log.text,1)-- Absence Event FROM [districtname].[dbo].[District] dis INNER JOIN [districtname].[dbo].[School] sch ON sch.number IN (SELECT id FROM @schoolIDs) AND sch.districtID = dis.districtID AND sch.districtID = @districtID INNER JOIN [districtname].[dbo].[Calendar] cal ON cal.districtID=dis.districtID AND cal.schoolID = sch.schoolID INNER JOIN [districtname].[dbo].[SchoolYear] scyr ON scyr.endYear = cal.endYear AND scyr.active = 1 INNER JOIN [districtname].[dbo].[Enrollment] enr ON enr.serviceType != 'S' AND enr.calendarID = cal.calendarID AND enr.active = 1 AND ( enr.noShow IS NULL OR enr.noShow = 0 ) INNER JOIN [districtname].[dbo].[Person] per ON per.personID = enr.personID INNER JOIN [districtname].[dbo].[Identity] id ON id.identityID=per.currentIdentityID INNER JOIN logs log ON log.personID = per.personID AND log.rowid = 1 ORDER BY per.studentNumber, log.dateTimeStamp |
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 ]
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 |
TSQL - Campus - Return (Local studentNumber, Students State ID, Students Last name, Students First name, Students Middle name, Students Birthday as dd/mm/yyyy, School name that they Graduated from, Date of Diploma Diploma Type)
posted Sep 21, 2017, 3:44 AM by Chris Franklin
SQLSELECT Person.studentNumber AS StudentID, Person.stateID AS StateID, [Identity].lastName AS LastName, [Identity].firstName AS FirstName, [Identity].middleName AS MiddleName, CONVERT(varchar(10),[Identity].birthdate,101) AS BirthDate, MAX(School.name) AS SchoolName, CONVERT(varchar(10),Graduation.diplomaDate,101) AS DiplomaDate, Graduation.diplomaType AS DiplomaType, CASE WHEN preference.value='1' THEN CONVERT(DECIMAL(4, 1), SUM(TranscriptCourse.gpaWeight * COALESCE(TranscriptCourse.gpaValue, TranscriptCourse.unweightedGpaValue))/SUM(TranscriptCourse.gpaWeight)) WHEN preference.value='2' THEN CONVERT(DECIMAL(5, 2), SUM(TranscriptCourse.gpaWeight * COALESCE(TranscriptCourse.gpaValue, TranscriptCourse.unweightedGpaValue))/SUM(TranscriptCourse.gpaWeight)) WHEN preference.value='4' THEN CONVERT(DECIMAL(7, 4), SUM(TranscriptCourse.gpaWeight * COALESCE(TranscriptCourse.gpaValue, TranscriptCourse.unweightedGpaValue))/SUM(TranscriptCourse.gpaWeight)) WHEN preference.value='5' THEN CONVERT(DECIMAL(8, 5), SUM(TranscriptCourse.gpaWeight * COALESCE(TranscriptCourse.gpaValue, TranscriptCourse.unweightedGpaValue))/SUM(TranscriptCourse.gpaWeight)) ELSE CONVERT(DECIMAL(6, 3), SUM(TranscriptCourse.gpaWeight * COALESCE(TranscriptCourse.gpaValue, TranscriptCourse.unweightedGpaValue))/SUM(TranscriptCourse.gpaWeight)) END cumGpa, creditsEarned AS CreditsEarned, creditsAttempted AS CreditsAttempted, MAX(Enrollment.grade) AS GradGrade FROM -- School District INNER JOIN Calendar ON Calendar.districtID = District.districtID INNER JOIN School ON School.schoolID = Calendar.schoolID AND School.type = 'PS' INNER JOIN Enrollment ON Enrollment.calendarID = Calendar.calendarID AND Enrollment.endStatus = '99' AND Enrollment.serviceType = 'P' AND ( Enrollment.noShow IS NULL OR Enrollment.noShow = 0 ) -- Personal INNER JOIN Person ON Person.personID=Enrollment.personID INNER JOIN Graduation ON Graduation.personID = Person.personID AND YEAR(graduation.diplomaDate) >= 2011 INNER JOIN [Identity] ON [Identity].identityID = Person.currentIdentityID -- GPA INNER JOIN GradeLevel ON GradeLevel.calendarID = Enrollment.calendarID AND GradeLevel.structureID = Enrollment.structureID AND COALESCE(GradeLevel.excludeGPA,0) = 0 INNER JOIN TranscriptCourse ON TranscriptCourse.personID = Person.personID AND TranscriptCourse.grade = GradeLevel.name AND ( TranscriptCourse.gpaValue IS NOT NULL OR TranscriptCourse.unweightedGpaValue IS NOT NULL ) LEFT OUTER JOIN preference ON preference.element='GPADigits' -- Credits INNER JOIN ( SELECT PersonID, SUM(creditsEarned) as creditsEarned, SUM(creditsAttempted) as creditsAttempted FROM ( SELECT TranscriptCredit.personID as personID, SUM(creditsEarned) as creditsEarned, SUM(creditsAttempted) as creditsAttempted FROM TranscriptCredit INNER JOIN TranscriptCourse ON TranscriptCourse.transcriptID = TranscriptCredit.transcriptID AND TranscriptCourse.personID = TranscriptCredit.personID INNER JOIN CurriculumStandard ON TranscriptCredit.standardID = CurriculumStandard.standardID INNER JOIN CurriculumStandard CurriculumStandard2 ON CurriculumStandard.parentID = CurriculumStandard2.standardID GROUP BY TranscriptCredit.personID, CurriculumStandard2.standardID, CurriculumStandard.standardID, CurriculumStandard.name, CurriculumStandard2.name ) AS Credits GROUP BY PersonID ) AS Credits ON Credits.personID = Person.personID WHERE District.inactive = 0 GROUP BY Person.personID, Person.studentNumber, Person.stateID, Enrollment.calendarID, [Identity].lastName, [Identity].firstName, [Identity].middleName, CONVERT(varchar(10),[Identity].birthdate,101), school.name, CONVERT(varchar(10),graduation.diplomaDate,101), graduation.diplomaType, preference.value, creditsEarned, creditsAttempted ORDER BY GradGrade, LastName, FirstName, MiddleName |
1-3 of 3