select
MD_Sect_SurrogateKey,
MF_SectGrade_CollID,
MD_Course_SurrogateKey,
MD_CarlTerm_SurrogateKey,
MD_Grade_SurrogateKey, -- The transcripted grade (not assigned grade)
MD_CollPers_SurrogateKey,
MD_CollPers_SurrogateKeyNonDegr,
MD_CollPers_SurrogateKeyFAidPers,
CASE
-- Assigned grades aren't as reliable as transcripted ones here, because we have no corresponding letter grade and thus can't exclude
-- non-aggregatable values as easily as we can with the transcripted ones (where we have the letter grade available here)
WHEN MF_SECTGRADE_ASSIGNEDGRADE = 0 THEN NULL
ELSE MF_SECTGRADE_ASSIGNEDGRADE
END AS MF_SectGrade_AssignedGrade,
CASE
-- We don't really have an assigned numeric grade on a scrunched course if we find a zero here
WHEN MF_SECTGRADE_ASSIGNEDGRADE = 0 THEN NULL
WHEN MF_SECTGRADE_ASSIGNEDGRADE IS NULL THEN NULL
WHEN MF_SECTGRADE_ATTEMPTEDCREDIT IS NULL THEN NULL
-- Use attempted credit here, because in the case of an F, no credit is completed, but there is still credit attempted
ELSE MF_SECTGRADE_ASSIGNEDGRADE * MF_SECTGRADE_ATTEMPTEDCREDIT
END AS MF_SECTGRADE_AssignedGradeTimesCreditsAttempted,
CASE
-- We don't really have an assigned numeric grade on a scrunched course if we find a zero here
WHEN MF_SECTGRADE_ASSIGNEDGRADE = 0 THEN NULL
WHEN MF_SECTGRADE_ASSIGNEDGRADE IS NULL THEN NULL
WHEN MF_SECTGRADE_ATTEMPTEDCREDIT IS NULL THEN NULL
-- Use attempted credit here, because in the case of an F, no credit is completed, but there is still credit attempted
ELSE MF_SECTGRADE_ATTEMPTEDCREDIT
END AS MF_SectGrade_AssignedGradedCreditsAttempted,
/*
* This attribute contains numeric equivalents to A, A-, B+, B, etc. (letter grades) only; no S, CR, NC, etc. equivalents are
* kept here because users could accidentally mix and match them, which would lead to false averages
*/
MF_SectGrade_TranscriptedGrade,
CASE
WHEN MF_SECTGRADE_TRANSCRIPTEDGRADE IS NULL THEN NULL
WHEN MF_SECTGRADE_ATTEMPTEDCREDIT IS NULL THEN NULL
-- Use attempted credit here, because in the case of an F, no credit is completed, but there is still credit attempted
ELSE MF_SECTGRADE_TRANSCRIPTEDGRADE * MF_SECTGRADE_ATTEMPTEDCREDIT
END AS MF_SectGrade_GradeTimesCreditsAttempted,
CASE
WHEN MF_SECTGRADE_TRANSCRIPTEDGRADE IS NULL THEN NULL
WHEN MF_SECTGRADE_ATTEMPTEDCREDIT IS NULL THEN NULL
-- Use attempted credit here, because in the case of an F, no credit is completed, but there is still credit attempted
ELSE MF_SECTGRADE_ATTEMPTEDCREDIT
END AS MF_SectGrade_GradedCreditsAttempted,
MF_SectGrade_CompletedCredit,
MF_SectGrade_AttemptedCredit

from
MF_SECTIONGRADE