School accreditation drill down analysis error

[Ghislain Hachey]

Seem that for this school the analysis of the school evaluations are all wrong.

‌

‌

[Ghislain Hachey]

The issue (and the solution) was in the data. During the transition from old to new school accreditation framework in FSM some data that was done using the new school accreditation framework had to be reloaded manually for various reason (e.g. improper use of tablet, data on tablet not the same and “corrected data” in excel). There was a bug in the deeper analysis for those that had to be loaded manually around 2021. This did not affect overall final level analysis of schools but deeper more advanced drill down analysis. The issue is that some school accreditation views considered those manually loaded as "legacy" school accreditation loosing all the information on sub-criteria and effectively not able to produce the actual school evaluation criteria scores. Below is the fix used so that the views would correctly identify those records as not legacy but "tablet" or second generation school accreditation.

The inspSource is usually a pointer to the actual tablet XML file but the absence of it considers the record legacy which is the cases of manually loaded records is not always the case.

BEGIN TRANSACTION;

SELECT *
  FROM [dbo].[SchoolInspection]
  WHERE [inspNote] = '[Loaded from manual record]';

SELECT *
  FROM [FEDEMIS].[pInspectionRead].[SCHOOL_ACCREDITATION]
  WHERE inspID IN (SELECT inspID FROM [dbo].[SchoolInspection] WHERE [inspNote] = '[Loaded from manual record]');

UPDATE [dbo].[SchoolInspection] SET inspSource = 1 WHERE [inspNote] = '[Loaded from manual record]' AND [inspSource] IS NULL;

SELECT *
  FROM [dbo].[SchoolInspection]
  WHERE [inspNote] = '[Loaded from manual record]';

SELECT *
  FROM [FEDEMIS].[pInspectionRead].[SCHOOL_ACCREDITATION]
  WHERE inspID IN (SELECT inspID FROM [dbo].[SchoolInspection] WHERE [inspNote] = '[Loaded from manual record]');

ROLLBACK;

‌