Use Case:
For the October 1 Child Count, the student must be enrolled in the school and receiving services to be included. Each student for whom the count date occurs on or between the entry date and the exit withdraw date shall be identified as one of Present - Receiving Services, Absent - Receiving Services, or Not Receiving Services. This element must NOT be sent for enrollment periods that do not include the count date. Leave the field blank.
Model Navigation
|
Oct1CountReport (DRAFT ONLY) |
---|
DROP VIEW edfi.Oct1CountReport CREATE VIEW edfi.Oct1CountReport AS SELECT (SELECT schoolyear from edfi.schoolyeartype where schoolyear = '2011') 'School Year', sch.localeducationagencyId 'LEA Id', eOrg.NameOfInstitution 'LEA', ssa.SchoolId 'School Id', e.NameOfInstitution 'School Name', stu.StudentUniqueId, stu.FirstName, stu.LastSurname, stu.MiddleName, stu.BirthDate, DATEDIFF(year, birthdate, '2010-10-01') 'Age', d.CodeValue 'Gender', de.CodeValue 'Limited English Proficiency', SEOA.HispanicLatinoEthnicity, ssa.EntryDate 'Enrollment Date', g.CodeValue 'Grade Level', '10/01/2010' AS 'Count Date', 1.0 as 'FTE', 'Present' As 'Attendance' from edfi.StudentSchoolAssociation ssa INNER JOIN edfi.School sch ON sch.schoolId = ssa.SchoolId INNER JOIN edfi.EducationOrganization e ON e.EducationOrganizationId = sch.SchoolId INNER JOIN edfi.EducationOrganization eOrg ON eOrg.EducationOrganizationId = sch.LocalEducationAgencyId INNER JOIN edfi.Student stu ON stu.StudentUSI = ssa.StudentUSI INNER JOIN edfi.StudentEducationOrganizationAssociation seoa ON seoa.StudentUSI = stu.StudentUSI AND seoa.EducationOrganizationId = eOrg.EducationOrganizationId LEFT OUTER JOIN edfi.Descriptor d ON d.DescriptorId = seoa.SexDescriptorId LEFT OUTER JOIN edfi.Descriptor de ON de.DescriptorId = seoa.LimitedEnglishProficiencyDescriptorId LEFT OUtER JOIN edfi.Descriptor g ON g.DescriptorId = ssa.EntryGradeLevelDescriptorId WHERE ssa.EntryDate <= '10/01/2010' and ssa.ExitWithdrawDate is null |
SELECT * FROM edfi.Oct1CountReport |
Special Education Student Count
WI - For the October 1 Child Count, the student must be receiving special education services under an active IEP or ISP, and the student must be assigned to a valid grade for his or her school to be included. The student's special education program association (SSEPA) record must also cover the count date. Effective Dates will be used to determine enrollment in the case of overlapping records. Additionally, the student must be between the ages of 3-21 on the count date and IDEA eligible.
AZ - OCT1 FED SPED Report provides a list of students receiving special student services with an indicator of whether the student was counted on the federally reported ESS October 1 count.
Model Navigation
|
Special Education Count Report (DRAFT ONLY) |
---|
DROP VIEW edfi.SpecialEdCountReport CREATE VIEW edfi.SpecialEdCountReport AS SELECT (SELECT schoolyear FROM edfi.schoolyeartype where schoolyear = '2011') 'School Year', stu.FirstName, stu.LastSurname, stu.MiddleName, stu.StudentUniqueId, stu.BirthDate, DATEDIFF(year, birthdate, '2010-10-01') 'Age', d.CodeValue 'Gender', rd.CodeValue 'Race', SEOA.HispanicLatinoEthnicity, de.CodeValue 'LEP', ssa.EntryDate 'Enrollment Date', gd.CodeValue 'Grade Level', SSEPA.ProgramName 'Program Name', pd.codevalue 'Program Type', SSEPA.BeginDate 'Special Education Begin Date' --D.DisabilityDescriptorId, --SSEOA.IEPBeginDate 'Entry Date', --SSEOA.IEPEndDate 'Exit Date', --SVC.PrimaryIndicator, --SVC.ServiceBeginDate, --SVC.ServiceEndDate FROM edfi.StudentSpecialEducationProgramAssociation SSEPA INNER JOIN edfi.Student stu ON stu.StudentUSI = SSEPA.StudentUSI INNER JOIN edfi.StudentSchoolAssociation SSA on SSA.StudentUSI = SSEPA.StudentUSI AND SSA.EntryDate is not null AND SSA.ExitWithdrawDate is null INNER JOIN edfi.StudentEducationOrganizationAssociation SEOA on SEOA.StudentUSI = SSEPA.StudentUSI AND SEOA.EducationOrganizationId = SSEPA.EducationOrganizationId INNER JOIN edfi.StudentEducationOrganizationAssociationRace R on R.StudentUSI = SSEPA.StudentUSI AND R.EducationOrganizationId = SSEPA.EducationOrganizationId LEFT OUTER JOIN edfi.Descriptor d ON d.DescriptorId = seoa.SexDescriptorId LEFT OUTER JOIN edfi.Descriptor de ON de.DescriptorId = seoa.LimitedEnglishProficiencyDescriptorId LEFT OUTER JOIN edfi.Descriptor rd ON rd.DescriptorId = r.RaceDescriptorId LEFT OUTER JOIN edfi.Descriptor gd ON gd.descriptorId = ssa.EntryGradeLevelDescriptorId LEFT OUTER JOIN edfi.Descriptor pd ON pd.DescriptorId = SSEPA.ProgramTypeDescriptorId --INNER JOIN edfi.StudentSpecialEducationProgramAssociationDisability Dis -- ON Dis.StudentUSI = SSEPA.StudentUSI AND Dis.EducationOrganizationId = SSEPA.EducationOrganizationId AND -- Dis.BeginDate = SSEPA.BeginDate AND Dis.ProgramEducationOrganizationId = SSEPA.ProgramEducationOrganizationId AND -- Dis.ProgramName = SSEPA.ProgramName AND Dis.ProgramTypeDescriptorId = SSEPA.ProgramTypeDescriptorId --LEFT OUTER JOIN edfi.Descriptor dd ON dd.DescriptorId = dis.DisabilityDescriptorId --INNER JOIN edfi.StudentSpecialEducationProgramAssociationSpecialEducationProgramService SVC --on SVC.StudentUSI = SSEPA.StudentUSI AND SVC.EducationOrganizationId = SSEPA.EducationOrganizationId AND --SVC.BeginDate = SSEPA.BeginDate AND SVC.ProgramEducationOrganizationId = SSEPA.ProgramEducationOrganizationId AND SVC.ProgramName = SSEPA.ProgramName AND SVC.ProgramTypeDescriptorId = SSEPA.ProgramTypeDescriptorId WHERE SSEPA.BeginDate <= '10/01/2010' |
SELECT * FROM edfi.SpecialEdCountReport |