MSSQL ROUND and SUM problem with 0.01 Value

Introduction

Sometimes when you are dealing with sql rounding you can run into problem with 0.01 rest when you sum some values. This is very annoying and many companies implement their own algorithm to properly round values that they match sum. For example you got sum of two numbers: d1 = 20.1597, d2 = 8.8452, and sum of them should be 29.0049. Someone finally round this value to 29.00 using this algorithm: sum = round(d1 + d2). Simple as rolling dice. But when comes to SQL, another developer want to show each of value seperately and thus he or she want to cast this value as varchar. Oh yeah varchar. When you cast d1 and d2 from money to varchar you got strange behaviour:
CAST(@d1 as varchar(100)) gives output as 20.16
CAST(@d2 as varchar(100)) gives output as 8.85
Hence sum of this two values 20.16 + 8.85 gives 29.01. FACEPALM....

Solution of your nighmares with 0.01

Some of your collegues perhaps did solve this problem with very complicated alhorithms which can make headache when trying to figure out code but I'll focus on simplest way as possible. And I'll take advantage of SQL. No strange math.
First to do you need to get temp variables from @d1 and @d2 which are rounded and not-rounded values.

declare @d1_r money = ROUND(@d1,2)
DECLARE @d1_nr money = ROUND(@d1,2,1)


DECLARE @d2_r money = ROUND(@d2,2)
DECLARE @d2_nr money = ROUND(@d2,2,1)

Having this values we can do cartesian set and get values that match sum of 29.

DECLARE @tab table (val money)

INSERT INTO @tab
SELECT @d1
UNION
SELECT @d1_nr
UNION
SELECT @d1_r
UNION
SELECT @d2
UNION
SELECT @d2_nr
UNION
SELECT @d2_r

With this code above we can do cross join:

SELECT  t1.val
  , t2.val
FROM
 @tab t1
 CROSS JOIN @tab t2
WHERE
 t1.val + t2.val = @sum

Which will output:
20.16 8.84
20.15 8.85
8.85 20.15
8.84 20.16

When we need to get one value from this query we can simply write:

 DECLARE @t1_val money
 DECLARE @t2_val money
 
 /* Cartesian set where t1 + t2 = sum */
 SELECT TOP(1) @t1_val = t1.val, @t2_val = t2.val
  FROM
   @tab t1
   CROSS JOIN @tab t2
  WHERE
   t1.val + t2.val = @sum

IF (@variant = 1)
 return CAST(@t1_val as varchar(20))
else
 return CAST(@t2_val as varchar(20))

So having all code together I create function called fn_round_two_numbers:

CREATE FUNCTION [dbo].[fn_round_two_numbers]
(
 @d1 money,
 @d2 money,
 @sum money,
 @variant int
)
RETURNS VARCHAR(100)
AS
BEGIN
 /* @d1 rounded and not rounded  */
 DECLARE @d1_r money = ROUND(@d1,2)
 DECLARE @d1_nr money = ROUND(@d1,2,1)
 
 /* @d2  rounded and not rounded */
 DECLARE @d2_r money = ROUND(@d2,2)
 DECLARE @d2_nr money = ROUND(@d2,2,1)
 
 /* Temp table for all values */
 DECLARE @tab table
 (val money)
 
 /* Inserting into table */
 INSERT INTO @tab
 SELECT @d1
 UNION
 SELECT @d1_nr
 UNION
 SELECT @d1_r
 UNION
 SELECT @d2
 UNION
 SELECT @d2_nr
 UNION
 SELECT @d2_r
 
 DECLARE @t1_val money
 DECLARE @t2_val money
 
 /* Cartesian set where t1 + t2 = sum */
 SELECT TOP(1) @t1_val = t1.val, @t2_val = t2.val
  FROM
   @tab t1
   CROSS JOIN @tab t2
  WHERE
   t1.val + t2.val = @sum

IF (@variant = 1)
 return CAST(@t1_val as varchar(20))
else
 return CAST(@t2_val as varchar(20))
 
RETURN '!@#AGGR SOMETHING GOES WRONG' 
END

Concusions

With this function you are safe as turtle inside shell. You can simply extend this function to using n-number rounding.

And this is all. Thanks for reading.