# Solving Rounding Issues in Teradata: A Possible Solution

Roland Wenzlofsky

April 21, 2023

Assuming the given table definition, let us proceed.

``CREATE VOLATILE TABLE ROUNDING_PROBLEM(            AMOUNT DECIMAl(18,2),            MONTHS INTEGER) NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;``
``INSERT INTO ROUNDING_PROBLEM VALUES (10.25,3);``

Assuming our objective is to divide each amount by the corresponding number of months, the calculation required per record is as follows:

AMOUNT / MONTHS

Implementing this is not challenging but may result in Teradata rounding issues.

``SELECT The_Month,SUM(val)FROM(SELECT 1 as the_month ,AMOUNT/MONTHS AS val FROM ROUNDING_PROBLEM UNION ALL SELECT 2 the_month ,AMOUNT/MONTHS as val FROM ROUNDING_PROBLEMUNION ALLSELECT 3 the_month ,AMOUNT/MONTHS as val FROM ROUNDING_PROBLEM) xGROUP BY 1ORDER BY 1;``

Unfortunately, you will end up with this result:

The_Month        Sum(val)
1                      3,42
2                      3,42
3                      3,42
10,26

The result is 10.26, whereas it should be 10.25. Unfortunately, our table only provides two digits of precision, leaving us without a simple solution.

One solution is to create an extra record to hold the rounding difference. In my case, I included it in the last month.

``SELECT The_Month,SUM(val)FROM(SELECT 1 as the_month ,AMOUNT/MONTHS AS val FROM ROUNDING_PROBLEMUNION ALLSELECT 2 the_month ,AMOUNT/MONTHS as val FROM ROUNDING_PROBLEMUNION ALLSELECT 3 the_month ,AMOUNT/MONTHS as val FROM ROUNDING_PROBLEMUNION ALLSELECT 3, AMOUNT - (AMOUNT/MONTHS*MONTHS) FROM ROUNDING_PROBLEM) xGROUP BY 1ORDER BY 1;``

The_Month        Sum(val)
1                      3,42
2                      3,42
3                      3,41
10,25

The monthly totals can be summed for accurate aggregations. Additionally, a unique identifier can be assigned to the rounding discrepancies, differentiating them from standard records in storage.