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 |