> For the complete documentation index, see [llms.txt](https://center-for-the-integration-of-id.gitbook.io/generate-documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://center-for-the-integration-of-id.gitbook.io/generate-documentation/developer-guides/working-with-generate-code/unit-test-by-file-spec/app.fs009_testcase.md).

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


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://center-for-the-integration-of-id.gitbook.io/generate-documentation/developer-guides/working-with-generate-code/unit-test-by-file-spec/app.fs009_testcase.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.