Incomplete cleanup of PupilTables by procedure dbo.xfdfGrid
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
Comments (4)
-
reporter -
repo owner 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.
-
reporter Ok, makes sense. You can go ahead and execute the cleanup script. I will have a play at it on my local workstation.
-
repo owner - changed status to closed
Cleanup executed in production
- Log in to comment
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)?