[Bug]: 009 update the logic for excluding students

[lizbrunet]

File Specification Number and Name

None

What Happened?

Code was added recently to capture students that were still in special education at the end of the year and to exclude them from the MKC counts. For the MKC category the file specification states, ‘There does not need to be evidence that the students are continuing in special education only that the students are continuing in an educational program.’
When we added the code we are checking their ProgramParticipationEndDate in Staging.ProgramParticpationSecialEducation and based on the file spec that is incorrect. We need to update the exclusion logic to check EnrollmentEndDate instead.

This is the logic created and tested in KY

	if @reportCode in ('C009')
	begin

		if @toggleCatchmentSea = 'Entire state (students moving out of state)'
		begin

			select @sql = @sql + char(10) +
				'			if OBJECT_ID(''tempdb..#excludeStudents'') is not null drop table #excludeStudents' + char(10)

			select @sql = @sql + 
			'
			select distinct sppse.StudentIdentifierState
			into #excludeStudents
			from staging.ProgramParticipationSpecialEducation sppse
				inner join Staging.K12Enrollment ske
					on ske.StudentIdentifierState = sppse.StudentIdentifierState
					and isnull(ske.LeaIdentifierSeaAccountability, '''') = isnull(sppse.LeaIdentifierSeaAccountability, '''') 
					and isnull(ske.SchoolIdentifierSea, '''') = isnull(sppse.SchoolIdentifierSea, '''')
					and sppse.ProgramParticipationEndDate BETWEEN ske.EnrollmentEntryDate and isnull(ske.EnrollmentExitDate, ''' + @calculatedSYEndDate + ''')
			where IDEAIndicator = 1
			and isnull(ske.EnrollmentExitDate, ''' + @calculatedSYEndDate + ''') >= ''' + @calculatedSYEndDate + '''
			order by sppse.StudentIdentifierState
			' + char(10)

			select @sql = @sql + 
				'			CREATE INDEX IDX_excludeStudents ON #excludeStudents (StudentIdentifierState)' + char(10)

		end

	end	

Acceptance Criteria
• Setup N/A
• QA - Test 009 migration to ensure it runs without error and then validate that any student with an EnrollmentExitDate that is NULL or [SY]-06-30 is not included in the counts for the MKC category
• Migration steps
o Ex.) RDS - App.Wrapper_Migrate_Exiting_to_RDS
o Ex.) Reports - 009
• Requires updating of Test Case(s) Y/N Yes
o Test proof (screenshot of passed test, will have this as the ticket is completed)
• Documentation (does this require update in documentation, Gitbook edit?) Possibly, need to verify
o Release Notes (actual text to be included in release notes) The logic around the MKC counts was checking for students that were still in SPED at the end of the year so they would not be included. That logic was modified to look for students still enrolled at the end of year to match the file specification language.
• OSC impact - Does any part of this task require an OSC update? No
o If so, add the OSC ticket # after its created to the JIRA ticket
• ETL Documentation impact - Does any part of this task require an ETL Doc Template update?
o If so, add the OSC ticket # after its created to the JIRA ticket

Steps to Reproduce

No response

Version

No response

Logs or Error Output

Priority

Medium

Code of Conduct

• I agree to follow the Code of Conduct.