App.FS006_TestCase

Overview & Purpose

This stored procedure is designed to test the accuracy of EDFacts reporting for discipline data related to students with disabilities. It compares data generated from staging tables against data stored in the reporting data store (RDS) to verify that the counts match across various category sets and reporting levels.

Main Functions:

• Data Preparation

  Prepares staging data by joining multiple tables to create a comprehensive dataset of student discipline incidents

• Test Case Execution

  Executes multiple test cases to validate data at SEA and LEA levels across different category sets

Key Calculations:

• Data Preparation: Categorizes discipline durations into EDFacts reporting categories

  Formula: RemovalLength calculation based on DurationOfDisciplinaryAction

  Business Significance: Ensures proper classification of discipline incidents for federal reporting

  Example: When sum(DurationOfDisciplinaryAction) is between 0.5 and 10 days, RemovalLength = 'LTOREQ10'

• Test Case Execution: Counts unique students for each combination of reporting dimensions

  Formula: COUNT(DISTINCT StudentIdentifierState)

  Business Significance: Ensures accurate student counts for federal reporting

  Example: Counting students by DisciplineMethod, RemovalLength, and IdeaDisabilityType

Data Transformations:

• Mapping of database codes to EDFacts codes (e.g., Sex values to 'M'/'F')

• Categorization of discipline durations into EDFacts reporting categories

• Filtering of records based on date ranges and program participation

• Exclusion of LEAs that should not be reported federally

Expected Output:

A series of test case results stored in App.SqlUnitTestCaseResult that indicate whether the counts in the staging data match the counts in the reporting data store

Business Context

System: EDFacts Reporting System

Necessity: To ensure accurate federal reporting of discipline data for students with disabilities as required by IDEA

Business Rules:

• Students must be between ages 3 and 21 as of the child count date

• Only include discipline incidents that occurred within the school year

• Only include students with disabilities (IDEA indicator = 1)

• Exclude students in certain educational environments (PPPS)

• Discipline methods must be InSchool or OutOfSchool

• Exclude certain types of interim removals (REMDW, REMHO)

Result Usage:

Results are used to validate the accuracy of EDFacts reporting before submission to the federal government

Execution Frequency:

Annually, prior to EDFacts submission deadlines

Critical Periods:

• Prior to EDFacts submission deadlines for discipline data

Parameters

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

Source Tables

Staging.K12Enrollment

Business Purpose: Contains student enrollment data including demographic information and enrollment dates

Columns:

Name	Data Type	Business Purpose
StudentIdentifierState	VARCHAR	Unique identifier for students within the state
LeaIdentifierSeaAccountability	VARCHAR	Identifier for the Local Education Agency (LEA) responsible for the student

Staging.Discipline

Business Purpose: Contains information about disciplinary actions taken against students

Columns:

Name	Data Type	Business Purpose
StudentIdentifierState	VARCHAR	Unique identifier for students within the state
DisciplinaryActionStartDate	DATE	Date when the disciplinary action began

Staging.ProgramParticipationSpecialEducation

Business Purpose: Contains information about students' participation in special education programs

Columns:

Name	Data Type	Business Purpose
StudentIdentifierState	VARCHAR	Unique identifier for students within the state
IDEAIndicator	BIT or INT	Indicates whether the student is served under IDEA

RDS.ReportEDFactsK12StudentDisciplines

Business Purpose: Contains the official EDFacts reporting data for student disciplines

Columns:

Name	Data Type	Business Purpose
ReportCode	VARCHAR	Identifies the EDFacts file specification (e.g., 'C006')
DisciplineCount	INT	Count of students for a specific combination of reporting dimensions

Temporary Tables

#C006Staging

Purpose: Main staging table that combines data from multiple source tables for analysis

Columns:

Name	Data Type	Purpose/Calculation
StudentIdentifierState	VARCHAR	Direct from source
RemovalLength	VARCHAR	Derived from DurationOfDisciplinaryAction

#S_CSA, #S_CSB, #S_CSC, #S_CSD, #S_ST1

Purpose: Store SEA-level aggregated results for different category sets

Columns:

Name	Data Type	Purpose/Calculation
DisciplineMethod	VARCHAR	Direct from #C006Staging
StudentCount	INT	COUNT(DISTINCT StudentIdentifierState)

#L_CSA, #L_CSB, #L_CSC, #L_CSD, #L_ST1

Purpose: Store LEA-level aggregated results for different category sets

Columns:

Name	Data Type	Purpose/Calculation
LeaIdentifierSeaAccountability	VARCHAR	Direct from #C006Staging
StudentCount	INT	COUNT(DISTINCT StudentIdentifierState)

#excludedLeas

Purpose: Stores LEAs that should not be included in federal reporting

Columns:

Name	Data Type	Purpose/Calculation
LeaIdentifierSeaAccountability	VARCHAR	Selected from Staging.K12Organization

Potential Improvements

Performance

Description: Add indexes to temporary tables to improve join and aggregation performance

Benefits: Faster execution, especially for large datasets

Priority: Medium

Error Handling

Description: Add TRY/CATCH blocks to handle potential errors

Benefits: More robust execution and better error reporting

Priority: Medium

Code Organization

Description: Refactor repetitive test case code into a parameterized approach

Benefits: Reduced code duplication, easier maintenance

Priority: Low

Execution Steps

Step 1: Initialize and clean up temporary objects

Input Data: None

Transformations: DROP TABLE statements for temporary tables

IF OBJECT_ID('tempdb..#C006Staging') IS NOT NULL DROP TABLE #C006Staging

Step 2: Define or retrieve the test case in App.SqlUnitTest

Input Data: App.SqlUnitTest table

Transformations: Conditional INSERT or SELECT

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

Step 3: Clear previous test results

Input Data: App.SqlUnitTestCaseResult table

Transformations: DELETE statement

DELETE FROM App.SqlUnitTestCaseResult WHERE SqlUnitTestId = @SqlUnitTestId

Step 4: Set up date variables and parameters

Input Data: app.ToggleResponses and app.ToggleQuestions tables

Transformations: Date calculations and string manipulations

declare @SYStart varchar(10) = CAST('07/01/' + CAST(@SchoolYear - 1 AS VARCHAR(4)) AS DATE)

Step 5: Identify LEAs to exclude from reporting

Input Data: Staging.K12Organization table

Transformations: Filter based on reporting flags and operational status

INSERT INTO #excludedLeas SELECT DISTINCT LEAIdentifierSea FROM Staging.K12Organization WHERE LEA_IsReportedFederally = 0...

Step 6: Create main staging table with all required data

Input Data: Multiple staging tables (K12Enrollment, Discipline, ProgramParticipationSpecialEducation, etc.)

Transformations: Multiple joins, filters, and data transformations

SELECT ske.StudentIdentifierState, ske.LeaIdentifierSeaAccountability, ... INTO #C006Staging FROM Staging.K12Enrollment ske JOIN...

Step 7: Update removal length categories

Input Data: #C006Staging table

Transformations: Categorization of discipline durations

UPDATE s SET s.RemovalLength = tmp.RemovalLength FROM #C006Staging s INNER JOIN (...) tmp ON...

Step 8: Execute test cases for SEA-level reporting

Input Data: #C006Staging table

Transformations: Aggregation by various dimensions

SELECT DisciplineMethod, RemovalLength, IdeaDisabilityType, COUNT(DISTINCT StudentIdentifierState) AS StudentCount INTO #S_CSA FROM #C006Staging...

Step 9: Execute test cases for LEA-level reporting

Input Data: #C006Staging table, #excludedLeas table

Transformations: Aggregation by various dimensions including LEA

SELECT s.LeaIdentifierSeaAccountability, DisciplineMethod, RemovalLength, IdeaDisabilityType, COUNT(DISTINCT StudentIdentifierState) AS StudentCount INTO #L_CSA FROM #C006Staging s LEFT JOIN #excludedLeas elea...