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.  

Assessment Results Validation

This query will allow for validation of assessment results from multiple sources.

select a.AssessmentFamilyTitle,dp.ShortDescription as Period,dc.ShortDescription as Category
, sa.AssessmentTitle,s.LastSurname,s.FirstName,ds.ShortDescription as Subject
, dg.ShortDescription as GradeAssessed,sa.Version,sa.AdministrationDate
, dt.ShortDescription as ReportingType, sas.Result, rt.ShortDescription as ResultType
from edfi.StudentAssessment sa
left join edfi.Student s on s.StudentUSI=sa.StudentUSI
left join edfi.Descriptor ds on ds.DescriptorId=sa.AcademicSubjectDescriptorId
left join edfi.Descriptor dg on dg.DescriptorId=sa.AssessedGradeLevelDescriptorId
left join edfi.StudentAssessmentScoreResult sas on sas.StudentUSI=sa.StudentUSI and sas.AssessmentTitle=sa.AssessmentTitle and sas.AcademicSubjectDescriptorId=sa.AcademicSubjectDescriptorId and sas.AssessedGradeLevelDescriptorId=sa.AssessedGradeLevelDescriptorId and sas.Version=sa.Version and sas.AdministrationDate=sa.AdministrationDate
left join edfi.AssessmentReportingMethodType dt on dt.AssessmentReportingMethodTypeId=sas.AssessmentReportingMethodTypeId
left join edfi.ResultDatatypeType rt on rt.ResultDatatypeTypeId=sas.ResultDatatypeTypeId
left join edfi.Assessment a on a.AssessmentTitle=sa.AssessmentTitle and a.AssessedGradeLevelDescriptorId=sa.AssessedGradeLevelDescriptorId and a.Version=sa.Version and a.AcademicSubjectDescriptorId=sa.AcademicSubjectDescriptorId
left join edfi.Descriptor dp on dp.DescriptorId=a.AssessmentPeriodDescriptorId
left join edfi.Descriptor dc on dc.DescriptorId=a.AssessmentCategoryDescriptorId
order by s.LastSurname,s.FirstName,ds.ShortDescription,rt.ShortDescription,sa.AdministrationDate

 


 

Assessment Objective Results Validation

This query will allow for validation of assessment objective results from multiple sources.

select a.AssessmentFamilyTitle,dp1.ShortDescription as Period,dc.ShortDescription as Category
, sa.AssessmentTitle,s.LastSurname,s.FirstName,ds.ShortDescription as Subject,dg.ShortDescription as GradeAssessed,sa.Version,sa.AdministrationDate
, oa.Description as Objective, soap.PerformanceLevelMet as PLMet,dp.ShortDescription as PL
from edfi.StudentAssessment sa
left join edfi.Student s on s.StudentUSI=sa.StudentUSI
left join edfi.Descriptor ds on ds.DescriptorId=sa.AcademicSubjectDescriptorId
left join edfi.Descriptor dg on dg.DescriptorId=sa.AssessedGradeLevelDescriptorId
left join edfi.StudentAssessmentStudentObjectiveAssessmentPerformanceLevel soap on soap.StudentUSI=sa.StudentUSI and soap.AssessmentTitle=sa.AssessmentTitle and soap.AcademicSubjectDescriptorId=sa.AcademicSubjectDescriptorId and soap.AssessedGradeLevelDescriptorId=sa.AssessedGradeLevelDescriptorId and soap.Version=sa.Version and soap.AdministrationDate=sa.AdministrationDate
left join edfi.ObjectiveAssessment oa on oa.AssessmentTitle=soap.AssessmentTitle and oa.AcademicSubjectDescriptorId=soap.AcademicSubjectDescriptorId and oa.AssessedGradeLevelDescriptorId=soap.AssessedGradeLevelDescriptorId and oa.Version=sa.Version and oa.IdentificationCode=soap.IdentificationCode
left join edfi.Descriptor dp on dp.DescriptorId=soap.PerformanceLevelDescriptorId
left join edfi.Assessment a on a.AssessmentTitle=sa.AssessmentTitle and a.AssessedGradeLevelDescriptorId=sa.AssessedGradeLevelDescriptorId and a.Version=sa.Version and a.AcademicSubjectDescriptorId=sa.AcademicSubjectDescriptorId
left join edfi.Descriptor dp1 on dp1.DescriptorId=a.AssessmentPeriodDescriptorId
left join edfi.Descriptor dc on dc.DescriptorId=a.AssessmentCategoryDescriptorId
order by s.LastSurname,s.FirstName,ds.ShortDescription,oa.Description,sa.AdministrationDate

 


 

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

 


 

DATA FRESHNESS SCRIPT

Use this SQL Script to see which tables are populated, when they were first populated, and when they were last updated

select 'Assessment' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.Assessment
union 
select 'AssessmentItem' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.AssessmentItem
union
select 'AssessmentScore' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.AssessmentScore
union
select 'CalendarDate' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.CalendarDate
union 
select 'CalendarDateCalendarEvent' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.CalendarDateCalendarEvent
union 
select 'ClassPeriod' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.ClassPeriod
union 
select 'Cohort' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.Cohort
union 
select 'Course' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.Course
union 
select 'CourseOffering' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.CourseOffering
union 
select 'CourseTranscript' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.CourseTranscript
union 
select 'DisciplineAction' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.DisciplineAction
union 
select 'DisciplineActionDiscipline' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.DisciplineActionDiscipline
union 
select 'DisciplineActionDisciplineIncident' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.DisciplineActionDisciplineIncident
union 
select 'DisciplineIncident' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.DisciplineIncident
union 
select 'DisciplineIncidentBehavior' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.DisciplineIncidentBehavior
union 
select 'EducationOrganization' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.EducationOrganization
union 
select 'Grade' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.Grade
union 
select 'GradebookEntry' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.GradebookEntry
union 
select 'GradingPeriod' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.GradingPeriod
union 
select 'GraduationPlan' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.GraduationPlan
union 
select 'Intervention' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.Intervention
union 
select 'Location' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.Location
union 
select 'Parent' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.Parent
union 
select 'Program' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.Program
union 
select 'ReportCard' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.ReportCard
union 
select 'ReportCardGrade' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.ReportCardGrade
union 
select 'RestraintEvent' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.RestraintEvent
union 
select 'SchoolCategory' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.SchoolCategory
union 
select 'SchoolGradeLevel' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.SchoolGradeLevel
union 
select 'Section' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.Section
union 
select 'SectionAttendanceTakenEvent' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.SectionAttendanceTakenEvent
union 
select 'Session' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.Session
union 
select 'Staff' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.Staff
union 
select 'StaffEducationOrganizationAssignmentAssociation' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StaffEducationOrganizationAssignmentAssociation
union 
select 'StaffEducationOrganizationEmploymentAssociation' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StaffEducationOrganizationEmploymentAssociation
union 
select 'StaffSchoolAssociation' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StaffSchoolAssociation
union 
select 'StaffSectionAssociation' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StaffSectionAssociation
union 
select 'Student' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.Student
union 
select 'StudentAcademicRecord' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StudentAcademicRecord
union 
select 'StudentAssessment' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StudentAssessment
union 
select 'StudentAssessmentItem' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.StudentAssessmentItem
union 
select 'StudentAssessmentScoreResult' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.StudentAssessmentScoreResult
union 
select 'StudentCharacteristic' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.StudentCharacteristic
union 
select 'StudentCohortAssociation' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StudentCohortAssociation
union 
select 'StudentDisability' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.StudentDisability
union 
select 'StudentDisciplineIncidentAssociation' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StudentDisciplineIncidentAssociation
union 
select 'StudentIndicator' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.StudentIndicator
union 
select 'StudentLanguage' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.StudentLanguage
union 
select 'StudentParentAssociation' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StudentParentAssociation
union 
select 'StudentProgramParticipation' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.StudentProgramParticipation
union 
select 'StudentRace' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(CreateDate) as Updated,count(*) as RecordCount
from edfi.StudentRace
union 
select 'StudentSchoolAssociation' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StudentSchoolAssociation
union 
select 'StudentSchoolAttendanceEvent' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StudentSchoolAttendanceEvent
union 
select 'StudentSectionAssociation' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StudentSectionAssociation
union 
select 'StudentSectionAttendanceEvent' as Category, min (CreateDate) as Since, max(CreateDate) as Created, max(LastModifiedDate) as Updated,count(*) as RecordCount
from edfi.StudentSectionAttendanceEvent


 

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 Characteristic Verification

This query provides student characteristic information including things such as Homeless, Immigrant and Unaccompanied Youth information.

 

Student Enrollment Verification

This query provides detailed student enrollment and exit-withdraw information.(StudentSchoolAssociation)

select distinct eo.NameOfInstitution, s.LastSurname as StudentLastName, s.FirstName as StudentFirstName
, ssa.SchoolYear , ssa.EntryDate , dg.CodeValue as GradeLevel, dg.ShortDescription as GradeLevelDesc
, ssa.ExitWithdrawDate, de.CodeValue as ExitWithdrawCode, de.ShortDescription as ExitWithdrawDesc, ssa.PrimarySchool
, dr.CodeValue as ResidencyCode, de.ShortDescription as ResidencyDesc, ssa.ClassOfSchoolYear
, dp.CodeValue as GradPlanCode, dp.ShortDescription as GradPlanDesc,ssa.GraduationSchoolYear,ssa.RepeatGradeIndicator
from edfi.StudentSchoolAssociation 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 dg on dg.DescriptorId=ssa.EntryGradeLevelDescriptorId
left join edfi.Descriptor de on de.DescriptorId=ssa.ExitWithdrawTypeDescriptorId
left join edfi.Descriptor dr on dr.DescriptorId=ssa.ResidencyStatusDescriptorId
left join edfi.Descriptor dp on dp.DescriptorId=ssa.GraduationPlanTypeDescriptorId
order by eo.NameOfInstitution,s.LastSurname,s.FirstName

 


 

Student Indicator Verification

This query provides student indicator information including things such as At-Risk information

 

 

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 Program Verification

This query provides student program information including things such as Early Childhood, LEP and Special Education.

 

 

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