select
MD_Sect_SurrogateKey,
MF_SectGradeBT_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_SECTGRADEBT_ASSIGNEDGRADE = 0 THEN NULL
ELSE MF_SECTGRADEBT_ASSIGNEDGRADE
END AS MF_SectGradeBT_AssignedGrade,
CASE
-- We don't really have an assigned numeric grade on a scrunched course if we find a zero here
WHEN MF_SECTGRADEBT_ASSIGNEDGRADE = 0 THEN NULL
WHEN MF_SECTGRADEBT_ASSIGNEDGRADE IS NULL THEN NULL
WHEN MF_SECTGRADEBT_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_SECTGRADEBT_ASSIGNEDGRADE * MF_SECTGRADEBT_ATTEMPTEDCREDIT
END AS MF_SECTGRADEBT_AssignedGradeTimesCreditsAttempted,
CASE
-- We don't really have an assigned numeric grade on a scrunched course if we find a zero here
WHEN MF_SECTGRADEBT_ASSIGNEDGRADE = 0 THEN NULL
WHEN MF_SECTGRADEBT_ASSIGNEDGRADE IS NULL THEN NULL
WHEN MF_SECTGRADEBT_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_SECTGRADEBT_ATTEMPTEDCREDIT
END AS MF_SectGradeBT_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_SectGradeBT_TranscriptedGrade,
CASE
WHEN MF_SECTGRADEBT_TRANSCRIPTEDGRADE IS NULL THEN NULL
WHEN MF_SECTGRADEBT_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_SECTGRADEBT_TRANSCRIPTEDGRADE * MF_SECTGRADEBT_ATTEMPTEDCREDIT
END AS MF_SectGradeBT_GradeTimesCreditsAttempted,
CASE
WHEN MF_SECTGRADEBT_TRANSCRIPTEDGRADE IS NULL THEN NULL
WHEN MF_SECTGRADEBT_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_SECTGRADEBT_ATTEMPTEDCREDIT
END AS MF_SectGradeBT_GradedCreditsAttempted,
MF_SectGradeBT_CompletedCredit,
MF_SectGradeBT_AttemptedCredit

from
MF_SECTIONGRADEBYTERM