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:

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.

Get the next issue

AMOUNT / MONTHS

Get the next issue by email.

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 adjustment records, differentiating them from standard records.

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe before the paid plan launches and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.