'How to divide and ROUND() a decimal while keeping the SUM() correct?

Consider this query as an example; table A represents the employee's attendance (10.33 on a specific day) and table B determines the percentage of his expected daily task distribution (50% working hard, 50% having fun):

--For simplification, actual tables ("Attendance" and "AttendanceCalculator") are converted to subselects
SELECT 
  Attendance.LoginName, 
  Attendance.AttendanceDate, 
  AttendanceExpectedActivity.ExpectedActivityDescription, 
  ROUND(
    Attendance.Total * (
      AttendanceExpectedActivity.ExpectedActivityPercentage / 100
    ), 
    2
  ) TotalActivity 
FROM 
  (
    SELECT 
      'johnd' as LoginName, 
      CAST(
        GETDATE() AS DATE
      ) AS AttendanceDate, 
      10.33 AS Total
  ) Attendance 
  INNER JOIN (
    SELECT 
      'johnd' as LoginName, 
      'Actually working hard' AS ExpectedActivityDescription, 
      CAST(50 AS float) AS ExpectedActivityPercentage 
    UNION ALL 
    SELECT 
      'johnd' as LoginName, 
      'Having fun!' AS ExpectedActivityDescription, 
      CAST(50 AS float) AS ExpectedActivityPercentage
  ) As AttendanceExpectedActivity ON AttendanceExpectedActivity.LoginName = Attendance.LoginName

This would bring the following result:

enter image description here

...Which sums to 10.34 instead of 10.33.

Ideally - you would want to ROUND() raw data, obviously, but in certain cases, this moves up the problem from the data layer to the application layer (as the numbers are being presented to the user with 2 decimal places for friendliness).

I'd like to prevent these cases on the data layer and treat it as a mathematical problem (while I'm having trouble defining the arithmetical problem...). In other words, in this specific case, I would want to have one row CEILed and one FLOORed.

Thanks



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source