Form B in RMI has a discrepency

[Ghislain Hachey]

The result in list view differs from the one in full preview.

‌

[Ghislain Hachey]

The difference seems to come from the difference in CO Level values. The ones from the UI is not the same as the one from the view pInspectionREad.SCHOOL_ACCREDITATION

‌

[Ghislain Hachey]

I think the problem is either of the following.

The SSACRIT (equal partitions) should be the one used to compute the level of the Classroom Observations in RMI.

Or, perhaps the error is in how the Partitions are configured in RMI.

If fact, I think the fix is more correct in the Partition table configuration. The SSA partitions should also be even partitions in RMI just like they already are for SSCRIT.

[Ghislain Hachey]

The fix. adjust the SSA partitions in Partitions table and then re-compute the Result that is stored on the SchoolInspection table. The following script was used for this.

BEGIN TRANSACTION;

DECLARE @ssaBefore TABLE (
    schNo VARCHAR(10),
    inspResult VARCHAR(10),
    Result int
)
DECLARE @ssaAfter TABLE (
    schNo VARCHAR(10),
    inspResult VARCHAR(10),
    Result int
)

SELECT * FROM [dbo].[SchoolInspection]
WHERE inspID in (Select inspID FROM pInspectionRead.SchoolInspections
WHERE InspTypeCode = 'SCHOOL_ACCREDITATION')

SELECT inspResult, COUNT(*) Result FROM [dbo].[SchoolInspection]
WHERE inspID in (Select inspID FROM pInspectionRead.SchoolInspections WHERE InspTypeCode = 'SCHOOL_ACCREDITATION')
GROUP BY inspResult

INSERT INTO @ssaBefore
SELECT schNo, inspResult, COUNT(*) Result FROM [dbo].[SchoolInspection]
WHERE inspID in (Select inspID FROM pInspectionRead.SchoolInspections WHERE InspTypeCode = 'SCHOOL_ACCREDITATION')
GROUP BY schNo, inspResult

SELECT * FROM @ssaBefore ORDER BY schNo, inspResult;

UPDATE [dbo].[SchoolInspection] SET inspResult = [dbo].[calcInspectionResult_RMI](inspID);

SELECT * FROM [dbo].[SchoolInspection]
WHERE inspID in (Select inspID FROM pInspectionRead.SchoolInspections
WHERE InspTypeCode = 'SCHOOL_ACCREDITATION')

SELECT inspResult, COUNT(*) Result FROM [dbo].[SchoolInspection]
WHERE inspID in (Select inspID FROM pInspectionRead.SchoolInspections WHERE InspTypeCode = 'SCHOOL_ACCREDITATION')
GROUP BY inspResult

INSERT INTO @ssaAfter
SELECT schNo, inspResult, COUNT(*) Result FROM [dbo].[SchoolInspection]
WHERE inspID in (Select inspID FROM pInspectionRead.SchoolInspections WHERE InspTypeCode = 'SCHOOL_ACCREDITATION')
GROUP BY schNo, inspResult

SELECT * FROM @ssaAfter ORDER BY schNo, inspResult;

SELECT * FROM @ssaBefore
EXCEPT
SELECT * FROM @ssaAfter
ORDER BY schNo, inspResult;

ROLLBACK;

‌

[Ghislain Hachey]

Fixed.