[Bug]: 194 - Young Homeless Children Served (McKinney-Vento)

[SDEdKy]

File Specification Number and Name

194 - Young Homeless Children Served (McKinney-Vento)

What Happened?

In RDS.Create_ReportData - some issues in the dynamic sql.

1. At line 71 in the SEA dynamic SQL - I added fact.SeaId to the select list because line 54 was looking for DimSeaID but it wasn't being called by the inner join.

2. At line 405-409 in the Lea and 401-404 in the Sea, I hardcoded @reportcode, @reportYear, @reportLevel, and @categorySetCode to get the SQL to run. I'm not completely clear if these variables somehow get passed when you run it from the stored procedure, but when I pulled the SQL out of debug mode they were coming up as errors. I'm sure it would be better to declare variables somewhere but just wanted to report that it causes an error as it currently is written.
   example:
   where f.ReportCode = 194 -- @reportcode
   and f.ReportYear = 2025 -- @reportYear
   and f.ReportLevel = 'lea' -- @reportLevel
   and f.CategorySetCode = isnull('csa'/@categorySetCode/, f.CategorySetCode)

3. Then there's a section below that where @dimSchoolYearId & @dimFactTypeId get declared again but they were already declared so I just commented out that section.

4. #cat_AGE gets created twice in the dynamic SQL, causing an error. First is on line 20ish, in the category set section. Then again around 449 in the #Cat_organizations section. I renamed the second instance of #cat_AGE to #cat_AGE_new (for all the #cat_AGE below approx line 449).

5. This one is really an error with our data, but it did cause the dynamic sql to fail so I wanted to mention it. We have one lea that doesn't have an NCES ID assigned to it in Generate. When it gets to the point where it inserts the data into the RDS.ReportEdFactsK12StudentCounts, it won't allow a null to be inserted into OrganizationIdentifierNCES and bombs out. For my purposes, I just added a line at 687 :
   --and [OrganizationIdentifierNces] is not null --
   to make it exclude nulls so I could at least get a file out to show all the other districts.

6. One more thing I noticed that did not cause an error but probably shouldn't exist is that there are some dates hardcoded in the dynamic sql. I saw some around line 172 for lea.RecordStartDateTime and lea.RecordEndDateTIme.

Steps to Reproduce

No response

Version

v13.0

Logs or Error Output

Priority

Medium

Code of Conduct

• I agree to follow the Code of Conduct.

[SDEdKy]

I didn't realize at the time I created the ticket but many of the same issues are affecting file 118 - Homeless Students Enrolled. Within the dynamic SQL there are 3 basic issues:

1. Some variables that were declared at the beginning get declared again and that causes an error.
2. Some variables (@reportcode, @reportYear, @categorySetCode) are referenced in the code but don't get declared anywhere.
3. There are temp tables that get used in the early part of the code and then get re-used later in the code. I'm actually not sure why that doesn't work if the table is dropped but it was causing an error for me. So I just renamed the second instance of the temp table and that seemed to work ok. For example #cat_GRADELEVEL was used early in the code so for the later instance, I renamed it #cat_GRADELEVEL_new.

[AEMJohnnyCunningham]

Shauna and I worked on this and there are 2 separate issues.
The first issue was with the code for the 194 and 118 files themselves. This probably would have impacted other files as well. There was one LEA record in the rds.DimLeas table that did not have an NCES Id. The column for that data in the rds.ReportEDFactsK12StudentCounts table does not accept nulls so that was causing a sql error. We have a fix for that and it will be included in the next release.
The second issue does not impact the population of the counts into the Report table but is more about trying to run the report data in debug mode to do validation work. In v13 we added the code that populates the zero counts into the report table. For those that are familiar, there is a process for executing the report migration in debug mode to get the code that will be executed by the report migration. You can then execute that code manually to validate counts, individual students, categories, etc... With the inclusion of the zero count logic when you get the sql code you now get the code that creates the counts followed by the code that creates the 0 rows. Those 2 sets of code were never run together before and there is some cleanup we need to do for that, duplicate variable declaration, temp tables, etc...

[SDEdKy]

Looks like in the section where you're allowing it to accept nulls, there's one little typo. When executing rds.create_reports, I get this error:

-- 2025 - 10 of 18 / 118-lea-CSA - |AGE3TOGRADE13|

Msg 50000, Level 15, State 1, Procedure rds.create_reports, Line 1366 [Batch Start Line 92]
Incorrect syntax near ', '.

There's just a missing apostrophe in the Category Options Organizations section:
isnull(s.LeaIdentifierNces, ') as OrganizationIdentifierNces,