overgrad_gpas

-- This generates an upload file for adding GPAs for HS students to Overgrad.
SELECT
  sites.site_name school
  , students.local_student_id local_id
  , max(CASE WHEN student_gpa.gpa_calculation_id = 2 THEN student_gpa.gpa ELSE 0 END) AS unweighted_uc_csu
  , max(CASE WHEN student_gpa.gpa_calculation_id = 3 THEN student_gpa.gpa ELSE 0 END) AS weighted_9_12

FROM
  matviews.ss_current

LEFT JOIN sites
    ON sites.site_id = matviews.ss_current.site_id
LEFT JOIN students
    ON students.student_id = matviews.ss_current.student_id
LEFT JOIN matviews.student_term_aff
    ON student_term_aff.student_id = matviews.ss_current.student_id
LEFT JOIN terms
    ON terms.term_id = matviews.student_term_aff.term_id
LEFT JOIN student_gpa
    ON students.student_id = student_gpa.student_id
LEFT JOIN gpa_calculations
    ON student_gpa.gpa_calculation_id = gpa_calculations.gpa_calculation_id

WHERE
  matviews.ss_current.site_id <> 9999999
  AND matviews.ss_current.grade_level_id >= 10
  AND student_gpa.academic_year is NULL
  AND terms.term_type = 1
  AND terms.term_name = 'Year'
  AND student_gpa.gpa_calculation_id IN (3, 2)
  AND student_gpa.grading_period_id IS NULL

GROUP BY
  sites.site_name
  , students.local_student_id

ORDER BY
  sites.site_name,
  local_student_id