Section Level Scores for KDE
Overview
KDE has requested de-normalized data for students scores at both total test and section level.
Example of De-Normalizing Section Scores.
This example returns student section scores for admin_id 1.
SELECT a_s_s_c.student_id, MAX(CASE WHEN a_s_s_c.section_id = 1 THEN a_s_s_c.percentage_score ELSE NULL END) AS Phonemic, MAX(CASE WHEN a_s_s_c.section_id = 3 THEN a_s_s_c.percentage_score ELSE NULL END) AS Phonics, MAX(CASE WHEN a_s_s_c.section_id = 4 THEN a_s_s_c.percentage_score ELSE NULL END) AS Vocabulary, MAX(CASE WHEN a_s_s_c.section_id = 5 THEN a_s_s_c.percentage_score ELSE NULL END) AS Comprehension, MAX(CASE WHEN a_s_s_c.section_id = 6 THEN a_s_s_c.percentage_score ELSE NULL END) AS Fluency FROM scores.admin_section_student_cache AS a_s_s_c WHERE a_s_s_c.admin_id = 1 GROUP BY a_s_s_c.student_id LIMIT 5;
Piecing Together Section Scores with Total Test Scores
We have a query for total test scores that we like to piece together with this one. The following example does that for admin id = 1
select client_assigned_id_2 as "SSID", upper(client_assigned_id_1) as "MGT ID", first_name, middle_name, last_name, birthdate, gender, percentage_score as "total test score", total_score_in_stanine as "total test stanine", total_score_in_percentile as "total test percentile", total_score_nce as "total test nce", client_assigned_id as "school ID", test.school, test.district, phonemic_pc, phonics_pc, vocabulary_pc, comprehension_pc, fluency_pc, phonemic_p, phonics_p, vocabulary_p, comprehension_p, fluency_p, phonemic_s, phonics_s, vocabulary_s, comprehension_s, fluency_s from ( select a_s_c.student_id, client_assigned_id_2, client_assigned_id_1, first_name, middle_name, last_name, birthdate, gender, a_s_c.percentage_score, a_s_c.total_score_in_stanine, a_s_c.total_score_in_percentile, a_s_c.total_score_nce, schools.client_assigned_id, orgs.name as school, orgs2.name as district, admins.grade_id from scores.admin_student_cache as a_s_c join assessment.administrations as admins on a_s_c.admin_id = admins.id join people.students as students on a_s_c.student_id = students.id join people.people as people on students.id = people.id join people.students2classes as s2c on students.id = s2c.student_id join organizations.schools as schools on s2c.school_id = schools.id join organizations.organizations as orgs on schools.id = orgs.id join organizations.districts as districts on schools.district_id = districts.id join organizations.organizations as orgs2 on districts.id = orgs2.id where s2c.admin_id in (1) order by last_name, first_name, middle_name) as test JOIN ( SELECT a_s_s_c.student_id, MAX(CASE WHEN a_s_s_c.section_id = 1 THEN a_s_s_c.percentage_score ELSE NULL END) AS phonemic_pc, MAX(CASE WHEN a_s_s_c.section_id = 3 THEN a_s_s_c.percentage_score ELSE NULL END) AS phonics_pc, MAX(CASE WHEN a_s_s_c.section_id = 4 THEN a_s_s_c.percentage_score ELSE NULL END) AS vocabulary_pc, MAX(CASE WHEN a_s_s_c.section_id = 5 THEN a_s_s_c.percentage_score ELSE NULL END) AS comprehension_pc, MAX(CASE WHEN a_s_s_c.section_id = 6 THEN a_s_s_c.percentage_score ELSE NULL END) AS fluency_pc, MAX(CASE WHEN a_s_s_c.section_id = 1 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS phonemic_p, MAX(CASE WHEN a_s_s_c.section_id = 3 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS phonics_p, MAX(CASE WHEN a_s_s_c.section_id = 4 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS vocabulary_p, MAX(CASE WHEN a_s_s_c.section_id = 5 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS comprehension_p, MAX(CASE WHEN a_s_s_c.section_id = 6 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS fluency_p, MAX(CASE WHEN a_s_s_c.section_id = 1 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS phonemic_s, MAX(CASE WHEN a_s_s_c.section_id = 3 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS phonics_s, MAX(CASE WHEN a_s_s_c.section_id = 4 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS vocabulary_s, MAX(CASE WHEN a_s_s_c.section_id = 5 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS comprehension_s, MAX(CASE WHEN a_s_s_c.section_id = 6 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS fluency_s FROM scores.admin_section_student_cache AS a_s_s_c JOIN assessment.sections as sections ON a_s_s_c.section_id = sections.id WHERE a_s_s_c.admin_id = 1 GROUP BY a_s_s_c.student_id ) AS subtest ON test.student_id = subtest.student_id LIMIT 5;
Kindergarten Example
select client_assigned_id_2 as "SSID", upper(client_assigned_id_1) as "MGT ID", first_name, middle_name, last_name, birthdate, gender, percentage_score as "total test score", total_score_in_stanine as "total test stanine", total_score_in_percentile as "total test percentile", total_score_nce as "total test nce", client_assigned_id as "school ID", test.school, test.district, phonemic_pc, early_literacy_pc, phonics_pc, vocabulary_pc, comprehension_pc, phonemic_p, early_literacy_p, phonics_p, vocabulary_p, comprehension_p, phonemic_s, early_literacy_s, phonics_s, vocabulary_s, comprehension_s from ( select a_s_c.student_id, client_assigned_id_2, client_assigned_id_1, first_name, middle_name, last_name, birthdate, gender, a_s_c.percentage_score, a_s_c.total_score_in_stanine, a_s_c.total_score_in_percentile, a_s_c.total_score_nce, schools.client_assigned_id, orgs.name as school, orgs2.name as district, admins.grade_id from scores.admin_student_cache as a_s_c join assessment.administrations as admins on a_s_c.admin_id = admins.id join people.students as students on a_s_c.student_id = students.id join people.people as people on students.id = people.id join people.students2classes as s2c on students.id = s2c.student_id join organizations.schools as schools on s2c.school_id = schools.id join organizations.organizations as orgs on schools.id = orgs.id join organizations.districts as districts on schools.district_id = districts.id join organizations.organizations as orgs2 on districts.id = orgs2.id where s2c.admin_id = 4 order by last_name, first_name, middle_name) as test JOIN ( SELECT a_s_s_c.student_id, MAX(CASE WHEN a_s_s_c.section_id = 1 THEN a_s_s_c.percentage_score ELSE NULL END) AS phonemic_pc, MAX(CASE WHEN a_s_s_c.section_id = 2 THEN a_s_s_c.percentage_score ELSE NULL END) AS early_literacy_pc, MAX(CASE WHEN a_s_s_c.section_id = 3 THEN a_s_s_c.percentage_score ELSE NULL END) AS phonics_pc, MAX(CASE WHEN a_s_s_c.section_id = 4 THEN a_s_s_c.percentage_score ELSE NULL END) AS vocabulary_pc, MAX(CASE WHEN a_s_s_c.section_id = 5 THEN a_s_s_c.percentage_score ELSE NULL END) AS comprehension_pc, MAX(CASE WHEN a_s_s_c.section_id = 1 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS phonemic_p, MAX(CASE WHEN a_s_s_c.section_id = 2 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS early_literacy_p, MAX(CASE WHEN a_s_s_c.section_id = 3 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS phonics_p, MAX(CASE WHEN a_s_s_c.section_id = 4 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS vocabulary_p, MAX(CASE WHEN a_s_s_c.section_id = 5 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS comprehension_p, MAX(CASE WHEN a_s_s_c.section_id = 1 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS phonemic_s, MAX(CASE WHEN a_s_s_c.section_id = 2 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS early_literacy_s, MAX(CASE WHEN a_s_s_c.section_id = 3 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS phonics_s, MAX(CASE WHEN a_s_s_c.section_id = 4 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS vocabulary_s, MAX(CASE WHEN a_s_s_c.section_id = 5 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS comprehension_s FROM scores.admin_section_student_cache AS a_s_s_c JOIN assessment.sections as sections ON a_s_s_c.section_id = sections.id WHERE a_s_s_c.admin_id = 4 GROUP BY a_s_s_c.student_id ) AS subtest ON test.student_id = subtest.student_id;
Third Grade Example
select client_assigned_id_2 as "SSID", upper(client_assigned_id_1) as "MGT ID", first_name, middle_name, last_name, birthdate, gender, percentage_score as "total test score", total_score_in_stanine as "total test stanine", total_score_in_percentile as "total test percentile", total_score_nce as "total test nce", client_assigned_id as "school ID", test.school, test.district, phonics_pc, vocabulary_pc, comprehension_pc, fluency_pc, research_pc, phonics_p, vocabulary_p, comprehension_p, fluency_p, research_p, phonics_s, vocabulary_s, comprehension_s, fluency_s, research_s from ( select a_s_c.student_id, client_assigned_id_2, client_assigned_id_1, first_name, middle_name, last_name, birthdate, gender, a_s_c.percentage_score, a_s_c.total_score_in_stanine, a_s_c.total_score_in_percentile, a_s_c.total_score_nce, schools.client_assigned_id, orgs.name as school, orgs2.name as district, admins.grade_id from scores.admin_student_cache as a_s_c join assessment.administrations as admins on a_s_c.admin_id = admins.id join people.students as students on a_s_c.student_id = students.id join people.people as people on students.id = people.id join people.students2classes as s2c on students.id = s2c.student_id join organizations.schools as schools on s2c.school_id = schools.id join organizations.organizations as orgs on schools.id = orgs.id join organizations.districts as districts on schools.district_id = districts.id join organizations.organizations as orgs2 on districts.id = orgs2.id where s2c.admin_id = 3 order by last_name, first_name, middle_name) as test JOIN ( SELECT a_s_s_c.student_id, MAX(CASE WHEN a_s_s_c.section_id = 3 THEN a_s_s_c.percentage_score ELSE NULL END) AS phonics_pc, MAX(CASE WHEN a_s_s_c.section_id = 4 THEN a_s_s_c.percentage_score ELSE NULL END) AS vocabulary_pc, MAX(CASE WHEN a_s_s_c.section_id = 5 THEN a_s_s_c.percentage_score ELSE NULL END) AS comprehension_pc, MAX(CASE WHEN a_s_s_c.section_id = 6 THEN a_s_s_c.percentage_score ELSE NULL END) AS fluency_pc, MAX(CASE WHEN a_s_s_c.section_id = 7 THEN a_s_s_c.percentage_score ELSE NULL END) AS research_pc, MAX(CASE WHEN a_s_s_c.section_id = 3 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS phonics_p, MAX(CASE WHEN a_s_s_c.section_id = 4 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS vocabulary_p, MAX(CASE WHEN a_s_s_c.section_id = 5 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS comprehension_p, MAX(CASE WHEN a_s_s_c.section_id = 6 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS fluency_p, MAX(CASE WHEN a_s_s_c.section_id = 7 THEN a_s_s_c.total_score_in_percentile ELSE NULL END) AS research_p, MAX(CASE WHEN a_s_s_c.section_id = 3 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS phonics_s, MAX(CASE WHEN a_s_s_c.section_id = 4 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS vocabulary_s, MAX(CASE WHEN a_s_s_c.section_id = 5 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS comprehension_s, MAX(CASE WHEN a_s_s_c.section_id = 6 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS fluency_s, MAX(CASE WHEN a_s_s_c.section_id = 7 THEN a_s_s_c.total_score_in_stanine ELSE NULL END) AS research_s FROM scores.admin_section_student_cache AS a_s_s_c JOIN assessment.sections as sections ON a_s_s_c.section_id = sections.id WHERE a_s_s_c.admin_id = 3 GROUP BY a_s_s_c.student_id ) AS subtest ON test.student_id = subtest.student_id;
Showing changes from previous revision. Removed | Added
