Solving Rounding Issues in Teradata: A Possible Solution

This article presents a potential solution for managing rounding discrepancies in Teradata.

Given the following 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_PROBLEM
UNION ALL
SELECT 3 the_month ,AMOUNT/MONTHS as val FROM ROUNDING_PROBLEM
) x
GROUP BY 1
ORDER 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_PROBLEM
UNION ALL
SELECT 2 the_month ,AMOUNT/MONTHS as val FROM ROUNDING_PROBLEM
UNION ALL
SELECT 3 the_month ,AMOUNT/MONTHS as val FROM ROUNDING_PROBLEM
UNION ALL
SELECT 3, AMOUNT - (AMOUNT/MONTHS*MONTHS) FROM ROUNDING_PROBLEM
) x
GROUP BY 1
ORDER 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 adjustment records, differentiating them from standard records.