Assessment to Enrollment Update

Find the values that need to update the enrollment record

SELECT

assessment_results.students_id,  

assessment_codes.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,

subjects.name,  // why twice?

student_terms.name,

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

 

# '$s..' Injects Assessment code ID, student ID and Term ID  (variables passed to this subroutine)

# finds the set of assessment results for a given student, given term, and at final stage of the term.

 where assessment_results.students_id=" . $sID . " 

 and assessment_results.student_term_id=" . $sTermId . " 

and assessment_results.stage=266   //  term final assessments (stage=266)

 

# find the set of final assessment results with matching subjects, as defined in the assessment code.

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   // sub

   limit 1

)  

group by assessment_codes.subjects_id, assessment_results.student_term_id;

 

Assessment Result Sample Data

Raw Score	Student	Terms	Stage	Assessment Code	Assessmentcode.Subject	Assessmentcode.Exclude	Improvement
540	183	Spring 2025 183	final	IXL.OP	Math	YES	calculated relative to other assessment records
620	183	Spring 2025 183	final	IXL.MAT	Math	YES
530	183	Spring 2025 183	final	IXL.GRAM	English	YES
490	183	Spring 2025 183	final	IXL.VOCAB	English	NO

Enrollments Sample Data

Student	Terms	class.units	Class	curriclum.Subject	Curriculum	Average Metics
183	Spring 2025 183	5	Math 4A	math	Math 5A	from assessment results
183	Spring 2025 183	5	Math 4A	math	Math 5B
183	Spring 2025 183	5	English 4A	english	English 4A
183	Spring 2025 183	5	Science 4A	life science	Biology