Incomplete cleanup of PupilTables by procedure dbo.xfdfGrid

[Ghislain Hachey]

Kirieta from Kiribati reported the following oddity.

FYI and assistance please, I kind of stumbled upon the data below while working with a newly downloaded enrolment workbook from KEMIS.

AND below

‌

[Ghislain Hachey]

This is from the data workbooks downloadable. This one in particular.

Looking at the source of this the data comes from warehouse.enrol which is build using dbo.PupilTables among others. dbo.PupilTables is populated through the annual census (PDF survey in Kiribati) and the responsible procedure for doing this when user uploads the PDF survey seems to be [dbo].[xfdfGrid].

However, there is no repeaters in the stored PDF survey that was loaded. I have also tried reloading the same survey with no repeaters and yet they remain in dbo.PupilTables. The reason for the lingering repeaters seem to stem from the below code which takes the approach on only cleaning up “specified rows and columns” which is more like an update.

That said, if a past survey was loaded with incorrect data (say repeaters from different class levels like this in case, they will not get cleaned up on reloading the different survey.

Is there a reason to only clean “specified rows and columns” as oppose to removing all data in dbo.PupilTables before re-inserting (which I believe is the approach taken when loading the workbook)?

‌

‌

[Brian Lewis]

The reason why it deletes data only from the rows and columns included in the grid is to allow the flexibility to have data collected in more than a single grisd. e.g. in Combined Schools, one grid for Primary and one Grid for Secondary. When you update the Secondary grid, you should only clear out secondary data, otherwise, updating the secondary grid would remove primary data, then updating the primary grid would put primary data back and remove secondary data.

You are right that if there is bad data in there, it will not get removed because the bad class level is not in the set of class levels being updated. The problem is not xfxGrid, which works as required, but that, at some stage, a school has been uploaded from the wrong PDF.

‌

This little SQL will find all PupilTable records where the referenced level is not a valid level for the type of the school (in that year). in KEMIS data this yields14 hits, including the ones in your example (KPS019 2021 Repeaters). These are the only such records found since 2005.

Select SS.schNo, SS.svyYear, SS.ssSchType, M.tlmLevel, PT.* 
from PupilTables PT
INNER JOIN SchoolSurvey SS
ON PT.ssID = ss.ssID
LEFT JOIN metaSchoolTypeLevelMap M
ON SS.ssschType = M.stCode
AND PT.ptLevel = M.tlmLevel
WHERE ptLevel is not null
and tlmLevel is null

This snippet deletes all these identified records from PupilTables - (note rollback):

‌

begin transaction

Select SS.schNo, SS.svyYear, SS.ssSchType, M.tlmLevel, PT.* 
from PupilTables PT
INNER JOIN SchoolSurvey SS
ON PT.ssID = ss.ssID
LEFT JOIN metaSchoolTypeLevelMap M
ON SS.ssschType = M.stCode
AND PT.ptLevel = M.tlmLevel
WHERE ptLevel is not null
and tlmLevel is null

select @@rowcount


DELETE from Pupiltables
from PupilTables PT
INNER JOIN SchoolSurvey SS
ON PT.ssID = ss.ssID
LEFT JOIN metaSchoolTypeLevelMap M
ON SS.ssschType = M.stCode
AND PT.ptLevel = M.tlmLevel
WHERE ptLevel is not null
and tlmLevel is null

select @@rowcount

Select SS.schNo, SS.svyYear, SS.ssSchType, M.tlmLevel, PT.* 
from PupilTables PT
INNER JOIN SchoolSurvey SS
ON PT.ssID = ss.ssID
LEFT JOIN metaSchoolTypeLevelMap M
ON SS.ssschType = M.stCode
AND PT.ptLevel = M.tlmLevel
WHERE ptLevel is not null
and tlmLevel is null

select @@rowcount

rollback

‌

Running this in production will remove the offending records, please advise and I’ll quickly do this, but leaving them there for the moment if you want to see them.

I suggest the fix then is just to do this cleanup, leave existing xfdfGrid working as intended.

[Ghislain Hachey]

Ok, makes sense. You can go ahead and execute the cleanup script. I will have a play at it on my local workstation.

[Brian Lewis]

Cleanup executed in production