App.FS009_TestCase

Overview & Purpose

This stored procedure is a unit test case for validating the C009 report (Special Education Exit) data generation. It compares data generated by the reporting system against a manually calculated dataset to ensure accuracy across various demographic breakdowns and organizational levels.

Main Functions:

• Test Data Generation

  Creates test data sets for comparison with the EDFacts C009 report

• Report Validation

  Compares test data against actual report data to validate accuracy

Key Calculations:

• Test Data Generation: Counts unique students for each demographic breakdown

  Formula: COUNT(DISTINCT StudentIdentifierState)

  Business Significance: Ensures accurate student counts in special education exit reporting

  Example: Student counts by age, race, disability type, and exit reason

• Report Validation: Determines if the test case passed or failed

  Formula: CASE WHEN s.StudentCount = ISNULL(rreksc.StudentCount, -1) THEN 1 ELSE 0 END

  Business Significance: Ensures data integrity in federal reporting

  Example: Comparing student counts by disability type between test data and report data

Data Transformations:

• Filtering students by age range (14-21)

• Mapping disability codes to EDFacts reporting codes

• Mapping exit reason codes to EDFacts reporting codes

• Determining race/ethnicity categories based on federal reporting guidelines

• Calculating student age based on child count date

Expected Output:

A series of test case results stored in App.SqlUnitTestCaseResult indicating whether each demographic breakdown matches between the test data and the actual report data

Business Context

System: EDFacts Reporting System

Necessity: Ensures accurate federal reporting of special education exit data as required by IDEA

Business Rules:

• Students must be between ages 14-21

• Only include students with valid disability codes

• Exit reasons must map to valid EDFacts codes

• Data must be aggregated at both SEA (state) and LEA (district) levels

• Multiple demographic breakdowns must be calculated (race, gender, EL status, disability type)

Result Usage:

Validates the accuracy of C009 (Special Education Exit) report data before federal submission

Execution Frequency:

Annually before EDFacts submission deadline

Critical Periods:

• EDFacts submission window

Parameters

Parameter	Data Type	Purpose	Required
@SchoolYear	SMALLINT	Specifies the school year for which to run the test case	True

Source Tables

Staging.ProgramParticipationSpecialEducation

Business Purpose: Contains special education program participation data including exit information

Columns:

Name	Data Type	Business Purpose
StudentIdentifierState	VARCHAR	Unique identifier for students at state level
LeaIdentifierSeaAccountability	VARCHAR	Identifies the LEA responsible for the student
ProgramParticipationEndDate	DATETIME	Date when student exited special education
SpecialEducationExitReason	VARCHAR	Reason why student exited special education

Staging.K12Enrollment

Business Purpose: Contains student enrollment information

Columns:

Name	Data Type	Business Purpose
StudentIdentifierState	VARCHAR	Unique identifier for students at state level
Birthdate	DATETIME	Student's date of birth
HispanicLatinoEthnicity	BIT	Indicates if student is Hispanic/Latino
Sex	VARCHAR	Student's sex

Staging.IdeaDisabilityType

Business Purpose: Contains student disability information

Columns:

Name	Data Type	Business Purpose
IdeaDisabilityTypeCode	VARCHAR	Code indicating student's disability category
IsPrimaryDisability	BIT	Indicates if this is the student's primary disability

App.SqlUnitTest

Business Purpose: Stores unit test definitions

Columns:

Name	Data Type	Business Purpose
SqlUnitTestId	INT	Primary key for unit tests
UnitTestName	VARCHAR	Name of the unit test

RDS.ReportEDFactsK12StudentCounts

Business Purpose: Stores generated EDFacts report data

Columns:

Name	Data Type	Business Purpose
ReportCode	VARCHAR	Identifies the EDFacts report (C009)
ReportYear	INT	School year of the report
StudentCount	INT	Count of students in each category

Temporary Tables

#disabilityCodes

Purpose: Stores valid disability codes for the specified school year

Columns:

Name	Data Type	Purpose/Calculation
CategoryOptionCode	VARCHAR(60)	Direct from App.CategoryOptions

#catchmentType

Purpose: Stores the two catchment area types to test

Columns:

Name	Data Type	Purpose/Calculation
CatchmentArea	VARCHAR(100)	Hardcoded values

#staging

Purpose: Stores calculated test data for comparison with report data

Columns:

Name	Data Type	Purpose/Calculation
StudentIdentifierState	VARCHAR(100)	Direct from source
Age	VARCHAR(100)	Calculated based on birthdate and child count date
SpecialEducationExitReasonEdFactsCode	VARCHAR(100)	Mapped from source exit reason to EDFacts code

#stuLea

Purpose: Stores the latest special education exit record for each student by LEA

Columns:

Name	Data Type	Purpose/Calculation
StudentIdentifierState	VARCHAR(20)	Direct from source
LeaIdentifierSeaAccountability	VARCHAR(20)	Direct from source
SpecialEducationServicesExitDate	DATETIME	MAX(ProgramParticipationEndDate)

Potential Improvements

Performance

Description: Add indexes to temporary tables

Benefits: Improved join performance, especially for #staging which is used in multiple joins

Priority: Medium

Code Quality

Description: Consolidate redundant code in test case execution

Benefits: Reduced code duplication, easier maintenance

Priority: Medium

Error Handling

Description: Add more specific error handling for data issues

Benefits: Better diagnostics when tests fail due to data problems

Priority: Low

Execution Steps

Step 1: Initialize test case in App.SqlUnitTest

Input Data: None

Transformations: None

IF NOT EXISTS (SELECT 1 FROM App.SqlUnitTest WHERE UnitTestName = 'FS009_UnitTestCase') BEGIN INSERT INTO App.SqlUnitTest...

Step 2: Clear previous test results

Input Data: SqlUnitTestId

Transformations: None

DELETE FROM App.SqlUnitTestCaseResult WHERE SqlUnitTestId = @SqlUnitTestId

Step 3: Set up school year data migration

Input Data: @SchoolYear parameter

Transformations: None

UPDATE rds.DimSchoolYearDataMigrationTypes SET IsSelected = 1 FROM rds.DimSchoolYearDataMigrationTypes sydmt JOIN rds.DimSchoolYears sy ON sydmt.DimSchoolYearId = sy.DimSchoolYearId WHERE sy.SchoolYear = @SchoolYear

Step 4: Calculate reference dates

Input Data: @SchoolYear parameter

Transformations: Date calculations

select @ChildCountDate = CAST('10/01/' + cast(@SchoolYear - 1 AS Varchar(4)) AS DATETIME)

Step 5: Load disability codes

Input Data: App.CategoryOptions, App.Categories, App.ToggleResponses

Transformations: Filtering by category code and toggle response

INSERT INTO #disabilityCodes SELECT distinct o.CategoryOptionCode from app.CategoryOptions o inner join app.Categories c on o.CategoryId = c.CategoryId and c.CategoryCode = 'DISABCATIDEAEXIT'...

Step 6: Process each catchment area type

Input Data: #catchmentType temp table

Transformations: Multiple

DECLARE db_cursor CURSOR FOR SELECT CatchmentArea FROM #catchmentType

Step 7: Generate report data

Input Data: Multiple source tables

Transformations: Multiple

EXEC RDS.Create_ReportData 'C009', 'specedexit', 0

Step 8: Calculate test data

Input Data: Multiple source tables

Transformations: Multiple joins, filters, and mappings

INSERT INTO #staging SELECT DISTINCT ske.StudentIdentifierState, ske.LeaIdentifierSeaAccountability, ske.SchoolIdentifierSea, ske.Birthdate, CASE WHEN @ChildCountDate <= sppse.ProgramParticipationEndDate THEN [RDS].[Get_Age](ske.Birthdate, @ChildCountDate) ELSE [RDS].[Get_Age](ske.BirthDate, DATEADD(year, -1, @ChildCountDate)) END AS Age...

Step 9: Run test cases and record results

Input Data: #staging and RDS.ReportEDFactsK12StudentCounts

Transformations: Aggregations by various demographics

INSERT INTO App.SqlUnitTestCaseResult (SqlUnitTestId, TestCaseName, TestCaseDetails, ExpectedResult, ActualResult, Passed, TestDateTime) SELECT @SqlUnitTestId, 'CSA SEA Match All'...