School Accreditation correct rounding of final score in FSM

Refer to the issue ~~#1058~~ for related issues.

The requirement for rounding of the final score must be changed from floor to standard rounding up (90.1-90.4 => 90 and 90.5-90.9 => 91).

This affects

The scalar function that calculates the final score [calcInspectionResult_SSA]
Update all results in [dbo].[SchoolInspection] for records where it was a tablet the source of the data (not the old legacy surveys)
Update in the frontend I think there is a place where it calculates the final scoring there as well and not get it from the database views
Probably need a warehouse rebuild that contains frozen school accreditation views data

The 1. above is simply

‌

--Select @FinalScore = floor(@coScore * @coWeight +  @seScore * @seWeight)
Select @FinalScore = round(@coScore * @coWeight +  @seScore * @seWeight, 0)

The 2. above I think this should do.

‌

BEGIN TRANSACTION;

SELECT COUNT(*)
      ,[Source]
      ,[InspectionYear]
      ,[InspectionType]
      ,[InspectionResult]
  FROM [pInspectionRead].[SCHOOL_ACCREDITATION]
  GROUP BY 
       [Source]
      ,[InspectionYear]
      ,[InspectionType]
      ,[InspectionResult]
  ORDER BY 
       [Source]
      ,[InspectionYear]
      ,[InspectionType]
      ,[InspectionResult]

UPDATE [dbo].[SchoolInspection] SET [inspResult] = [dbo].[calcInspectionResult_SSA](inspID) WHERE [inspXml].value('(/survey/version)[1]', 'int') != 0

SELECT COUNT(*)
      ,[Source]
      ,[InspectionYear]
      ,[InspectionType]
      ,[InspectionResult]
  FROM [pInspectionRead].[SCHOOL_ACCREDITATION]
  GROUP BY 
       [Source]
      ,[InspectionYear]
      ,[InspectionType]
      ,[InspectionResult]
  ORDER BY 
       [Source]
      ,[InspectionYear]
      ,[InspectionType]
      ,[InspectionResult]

ROLLBACK

I think 3. above only needs this.

After a warehouse rebuild 4. _AND _the data modification as discussed in ~~#1058~~ were done I now seem to arrive at consistent results.

‌

Comments (8)