Assessment to Enrollment Update
Find the values that need to update the enrollment record
SELECT
assessment_results.students_id,
# grouping by subject ID, so these find the average for all assessment codes with same subject
avg(level_equivalent) as Final_LevelEq_enrollment ,
avg(dapdtterm) as APRate_enrollment,
avg(dapterm) as DAPDT_enrollment,
subjects.name, // why twice?
assessment_codes.subjects_id
FROM assessment_results
left join assessment_codes on assessment_results.assement_codes_id=assessment_codes.id
left join subjects on assessment_codes.subjects_id=subjects.id
left join student_terms on student_terms.id=assessment_results.student_term_id
# Injects Assessment code ID, student ID and Term ID (variables passed to this subroutine)
where assessment_results.students_id=" . $sID . "
and assessment_results.student_term_id=" . $sTermId . "
and assessment_results.stage=266 // only updated the enrollment for term final assessments (stage=266)
# get the matching set
and subjects.id=(
Select subjects_id //filtering results where the id of the subject matches the result of the subquery
from assessment_codes //looks up the subjects_id from the assessment_codes table
where id=" . $sCodeId . " and exclude_results!=1 // some assessment results are excluded from the averaging process, they are so marked in the assessment code record with a toggle.
limit 1
)
group by assessment_codes.subjects_id, assessment_results.student_term_id;