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