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:
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 bookAMOUNT / 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.
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