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 ENDBusiness 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
@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:
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:
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:
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:
SqlUnitTestId
INT
Primary key for unit tests
UnitTestName
VARCHAR
Name of the unit test
RDS.ReportEDFactsK12StudentCounts
Business Purpose: Stores generated EDFacts report data
Columns:
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:
CategoryOptionCode
VARCHAR(60)
Direct from App.CategoryOptions
#catchmentType
Purpose: Stores the two catchment area types to test
Columns:
CatchmentArea
VARCHAR(100)
Hardcoded values
#staging
Purpose: Stores calculated test data for comparison with report data
Columns:
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:
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'...
Was this helpful?