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)
Post date: Sep 21, 2017 10:44:57 AM
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
SQL
SELECT
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