'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:
...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 |
|---|

