Data Validation Queries

Data Validation Queries

 

The queries shown below can be used in the Custom Exports portion of the cockpit application to verify and validate parts information available in the district database.  

Student Language Usage Analysis

This query provides a breakdown of all of the languages indicated for students in the district, the usage type (home, spoken, dominant, etc.) and the total number of students with a given configuration.

select distinct d.CodeValue,d.ShortDescription as [LanguageDescriptor]
,lnt.Description as [LanguageType],lt.Description as [LanguageUse]
,count(*) as [Count]
from edfi.StudentLanguage sl
left join edfi.StudentLanguageUse slu on slu.StudentUSI=sl.StudentUSI and slu.LanguageDescriptorId=sl.LanguageDescriptorId
left join edfi.Descriptor d on d.DescriptorId=sl.LanguageDescriptorId
left join edfi.LanguageUseType lt on lt.LanguageUseTypeId=slu.LanguageUseTypeId
left join edfi.LanguageDescriptor ld on ld.LanguageDescriptorId=d.DescriptorId
left join edfi.LanguageType lnt on lnt.LanguageTypeId=ld.LanguageTypeId
group by d.CodeValue,d.ShortDescription,lnt.Description,lt.Description

 


 

 

Students Without PARENTS

This query indicates students who are enrolled in a school, but who do not have parent/contact records listed.

select eo.NameOfInstitution as Schools,StudentUniqueId as UIC,s.LastSurname,s.FirstName,d.ShortDescription as Grade,ssch.ExitWithdrawDate
from edfi.Student s
left join edfi.StudentSchoolAssociation ssch on ssch.StudentUSI=s.StudentUSI
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssch.SchoolId
left join edfi.Descriptor d on d.DescriptorId=ssch.EntryGradeLevelDescriptorId
left join edfi.StudentParentAssociation spa on spa.StudentUSI=s.StudentUSI
where spa.StudentUSI is null and ssch.StudentUSI is not null
order by s.LastSurname,s.FirstName

 


 

 

Students Without Schedules

This query indicates students who are enrolled in a school, but who do not have a schedule.

select eo.NameOfInstitution as Schools,StudentUniqueId as UIC,s.LastSurname,s.FirstName,d.ShortDescription as Grade,ssch.ExitWithdrawDate
from edfi.Student s
left join edfi.StudentSectionAssociation ssa on ssa.StudentUSI=s.StudentUSI
left join edfi.StudentSchoolAssociation ssch on ssch.StudentUSI=s.StudentUSI
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssch.SchoolId
left join edfi.Descriptor d on d.DescriptorId=ssch.EntryGradeLevelDescriptorId
where ssa.StudentUSI is null and ssch.StudentUSI is not null
order by s.LastSurname,s.FirstName