This article presents a potential resolution for managing rounding discrepancies in Teradata.
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_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 discrepancies, differentiating them from standard records in storage.
