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.  

Calendar Date Event Validation

This query shows the school year calendar for all schools in the system, allowing the user to make sure the correct descriptions are provided for each school day.  This is important for the MiExcel file creation.

select eo.NameOfInstitution,cal.Date,cal.EventDuration, d.CodeValue,d.ShortDescription,d.Description from edfi.CalendarDateCalendarEvent cal left join edfi.Descriptor d on d.DescriptorId=cal.CalendarEventDescriptorId left join edfi.EducationOrganization eo on eo.EducationOrganizationId=cal.SchoolId order by eo.NameOfInstitution,cal.Date

 


 

Class List Verification

This query will list class list information for every school, broken down by teacher, section, term and student. It is useful in determining if all class roster information is correctly transferring to the data hub database.

select distinct eo.NameOfInstitution,st.LastSurname as TeacherLastName, st.FirstName as TeacherFirstName 
  , c.CourseCode, c.CourseTitle,ssa.ClassPeriodName,dt.CodeValue
  , s.LastSurname as StudentLastName, s.FirstName as StudentFirstName
from edfi.StudentSectionAssociation ssa
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId
left join edfi.Student s on s.StudentUSI=ssa.StudentUSI
left join edfi.Descriptor dt on dt.DescriptorId=ssa.TermDescriptorId
left join edfi.StaffSectionAssociation stsa on stsa.ClassPeriodName=ssa.ClassPeriodName
  and ssa.ClassroomIdentificationCode=stsa.ClassroomIdentificationCode
  and ssa.LocalCourseCode=stsa.LocalCourseCode
  and ssa.SequenceOfCourse=stsa.SequenceOfCourse
  and ssa.UniqueSectionCode=stsa.UniqueSectionCode
  and ssa.TermDescriptorId=stsa.TermDescriptorId
left join edfi.Staff st on st.StaffUSI=stsa.StaffUSI
left join edfi.CourseOffering co on co.LocalCourseCode=ssa.LocalCourseCode
left join edfi.Course c on c.CourseCode=co.CourseCode
order by eo.NameOfInstitution,st.LastSurname,st.FirstName
,dt.CodeValue,ssa.ClassPeriodName,s.LastSurname,s.FirstName

 


 

DAILY ATTENDANCE VERIFICATION

This query will list daily attendance events for every student, by school, with details on grade level, absence type and calendar day type.

SELECT isnull(eo.NameOfInstitution,eo.ShortNameOfInstitution) as School
,s.LastSurname, s.FirstName, isnull(s.MiddleName,'') as MiddleName
,s.StudentUniqueId, dgl.CodeValue as GradeLevel, e.EventDate
,isnull(e.AttendanceEventReason,'') reason, d.ShortDescription AttCategory
,aect.Description as AttType, dt.ShortDescription term, dc.Description as DayDescription
FROM
edfi.StudentSchoolAttendanceEvent e
left JOIN edfi.student s ON s.StudentUSI = e.StudentUSI
left JOIN edfi.EducationOrganization eo ON eo.EducationOrganizationId = e.SchoolId
left JOIN edfi.Descriptor d ON d.DescriptorId = e.AttendanceEventCategoryDescriptorId
left JOIN edfi.Descriptor dt ON dt.DescriptorId = e.TermDescriptorId
left join edfi.CalendarDateCalendarEvent cd on cd.Date=e.EventDate and cd.SchoolId=e.SchoolId
left join edfi.Descriptor dc on dc.DescriptorId=cd.CalendarEventDescriptorId
left join edfi.StudentSchoolAssociation ssa on ssa.StudentUSI=e.StudentUSI and ssa.SchoolId=e.SchoolId
left join edfi.Descriptor dgl on dgl.DescriptorId=ssa.EntryGradeLevelDescriptorId
left join edfi.AttendanceEventCategoryDescriptor aecd on aecd.AttendanceEventCategoryDescriptorId=e.AttendanceEventCategoryDescriptorId
left join edfi.AttendanceEventCategoryType aect on aect.AttendanceEventCategoryTypeId=aecd.AttendanceEventCategoryTypeId
order by eo.NameOfInstitution,s.LastSurname,s.FirstName,e.EventDate

 


 

Discipline action taken verification

This query will list actions taken for students on discipline incidents

select da.DisciplineActionIdentifier,s.LastSurname,s.FirstName,eo.NameOfInstitution ,da.DisciplineDate,da.DisciplineActionLength,da.ActualDisciplineActionLength
,dt.Description as DisciplineType,d.Description as DisciplineDescription, d.ShortDescription as DisciplineShortDescription, d.CodeValue as DisciplineCodeValue
,da.LastModifiedDate,da.CreateDate,d.Namespace
from edfi.DisciplineAction da
left join edfi.Student s on s.StudentUSI=da.StudentUSI
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=da.ResponsibilitySchoolId
left join edfi.DisciplineActionDiscipline dad on dad.StudentUSI=s.StudentUSI and dad.DisciplineActionIdentifier=da.DisciplineActionIdentifier
left join edfi.DisciplineDescriptor dd on dd.DisciplineDescriptorId=dad.DisciplineDescriptorId
left join edfi.DisciplineType dt on dt.DisciplineTypeId=dd.DisciplineTypeId
left join edfi.Descriptor d on d.DescriptorId=dad.DisciplineDescriptorId
where da.ResponsibilitySchoolId=130 and da.DisciplineDate>'2017-07-01'
order by da.DisciplineDate, s.LastSurname,s.FirstName

 


 

SECTION ATTENDANCE VERIFICATION

This query provides a breakdown of all of the student absences by section.

SELECT isnull(eo.NameOfInstitution,eo.ShortNameOfInstitution) as School
,s.LastSurname, s.FirstName, isnull(s.MiddleName,'') as MiddleName
,s.StudentUniqueId, dgl.CodeValue as GradeLevel, e.EventDate
,isnull(e.AttendanceEventReason,'') reason, d.ShortDescription AttCategory
,aect.Description as AttType, dt.ShortDescription term, dc.Description as DayDescription
,co.LocalCourseTitle,co.CourseCode,e.LocalCourseCode,e.ClassPeriodName,e.ClassroomIdentificationCode
FROM edfi.studentsectionattendanceevent e
left join edfi.CourseOffering co on co.LocalCourseCode=e.LocalCourseCode and co.SchoolId=e.SchoolId and co.SchoolYear=e.SchoolYear and co.TermDescriptorId=e.TermDescriptorId
left JOIN edfi.student s ON s.StudentUSI = e.StudentUSI
left JOIN edfi.EducationOrganization eo ON eo.EducationOrganizationId = e.SchoolId
left JOIN edfi.Descriptor d ON d.DescriptorId = e.AttendanceEventCategoryDescriptorId
left JOIN edfi.Descriptor dt ON dt.DescriptorId = e.TermDescriptorId
left join edfi.CalendarDateCalendarEvent cd on cd.Date=e.EventDate and cd.SchoolId=e.SchoolId
left join edfi.Descriptor dc on dc.DescriptorId=cd.CalendarEventDescriptorId
left join edfi.StudentSchoolAssociation ssa on ssa.StudentUSI=e.StudentUSI and ssa.SchoolId=e.SchoolId
left join edfi.Descriptor dgl on dgl.DescriptorId=ssa.EntryGradeLevelDescriptorId
left join edfi.AttendanceEventCategoryDescriptor aecd on aecd.AttendanceEventCategoryDescriptorId=e.AttendanceEventCategoryDescriptorId
left join edfi.AttendanceEventCategoryType aect on aect.AttendanceEventCategoryTypeId=aecd.AttendanceEventCategoryTypeId
order by eo.NameOfInstitution,s.LastSurname,s.FirstName,e.EventDate

 


 

 

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

 


 

 

Student Schedule Verification

This query lists the schedules for students in all schools, sorted by school, student, term and class period.

select distinct eo.NameOfInstitution, s.LastSurname as StudentLastName, s.FirstName as StudentFirstName
  , c.CourseCode, c.CourseTitle,ssa.ClassPeriodName,dt.CodeValue
  , st.LastSurname as TeacherLastName, st.FirstName as TeacherFirstName, ssa.ClassroomIdentificationCode
from edfi.StudentSectionAssociation ssa
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId
left join edfi.Student s on s.StudentUSI=ssa.StudentUSI
left join edfi.Descriptor dt on dt.DescriptorId=ssa.TermDescriptorId
left join edfi.StaffSectionAssociation stsa on stsa.ClassPeriodName=ssa.ClassPeriodName
  and ssa.ClassroomIdentificationCode=stsa.ClassroomIdentificationCode
  and ssa.LocalCourseCode=stsa.LocalCourseCode
  and ssa.SequenceOfCourse=stsa.SequenceOfCourse
  and ssa.UniqueSectionCode=stsa.UniqueSectionCode
  and ssa.TermDescriptorId=stsa.TermDescriptorId
left join edfi.Staff st on st.StaffUSI=stsa.StaffUSI
left join edfi.CourseOffering co on co.LocalCourseCode=ssa.LocalCourseCode
left join edfi.Course c on c.CourseCode=co.CourseCode
order by eo.NameOfInstitution,s.LastSurname,s.FirstName,dt.CodeValue,ssa.ClassPeriodName

 


 

 

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