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

Comments