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