# 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'...