DWHPro Insights

Solving Rounding Issues in Teradata: A Possible Solution

By Roland Wenzlofsky ·

Solving Rounding Issues in Teradata: A Possible Solution

Scope: this is a three-period allocation example for AMOUNT 10.25 and MONTHS 3, not a general allocator for arbitrary MONTHS values. The demonstrated result is 3.42, 3.42 and 3.41. A reusable version must generate the required number of periods, define decimal precision and rounding, and explicitly handle zero, NULL and negative inputs.

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:

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

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

For this three-period fixture, assigning the residual to the third period preserves the 10.25 total. Keep a source-row identifier if several allocations are combined, and define how residuals are assigned before generalizing the approach.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam