# As Built # Assessment to Enrollment Update #### Find the values that need to update the enrollment record > SELECT > > assessment\_results.students\_id, > > [assessment\_codes.id](http://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](http://subjects.name), > > [subjects.name](http://subjects.name), // why twice? > > [student\_terms.name](http://student_terms.name), > > assessment\_codes.subjects\_id > > FROM assessment\_results > > left join assessment\_codes on assessment\_results.assement\_codes\_id=[assessment\_codes.id](http://assessment_codes.id) > > left join subjects on assessment\_codes.subjects\_id=[subjects.id](http://subjects.id) > > left join student\_terms on [student\_terms.id](http://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](http://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**
540183Spring 2025 183finalIXL.OPMathYES*calculated relative to other assessment records*
620183Spring 2025 183finalIXL.MATMathYES
530183Spring 2025 183finalIXL.GRAMEnglishYES
490183Spring 2025 183finalIXL.VOCABEnglishNO
#### Enrollments Sample Data
**Student****Terms****class.units****Class****curriclum.Subject****Curriculum****Average Metics**
183Spring 2025 1835Math 4AmathMath 5A*from assessment results*
183Spring 2025 1835Math 4AmathMath 5B
183Spring 2025 1835English 4AenglishEnglish 4A
183Spring 2025 1835Science 4Alife scienceBiology