May 31, 2011 at 6:33 pm
I have what ought to be a simple problem, but is more subtle than I thought; what data type to use with Money in calculations involving multiple and divide.
I have inherited some messy code that multiplies or divides by numbers like 1.175 or 1.2 depending on order date, (British readers will recognise VAT calculations). So I wrote VAT() and UnVAT() functions to hide the complexity in functions, e.g.
CREATE FUNCTION [dbo].VAT (
@Net_Amount [money],
@Order_Date [date] )
RETURNS money
AS
BEGIN
DECLARE @VAT_Rate [real] =
CASE
WHEN @Order_Date >= '2011-01-04' then 0.200
WHEN @Order_Date >= '2010-01-01' then 0.175
WHEN @Order_Date >= '2008-12-01' then 0.150
WHEN @Order_Date >= '1991-01-01' then 0.175
WHEN @Order_Date >= '1979-06-18' then 0.150
WHEN @Order_Date >= '1974-07-01' then 0.080
WHEN @Order_Date >= '1973-04-01' then 0.100
ELSE 0.000
END
RETURN ( @Net_Amount * @VAT_Rate );
END
GO(Yes those are ALL the dates on which the UK VAT rate changed - my data doesn't go back to before 1973 but I like to be thorough!)
This replaces clauses like
CASE WHEN [OrderDate]> '20081130' AND [OrderDate]< '20100101' THEN [NetValue]*0.15 WHEN [OrderDate]> '20110103' THEN [NetValue]*0.2 ELSE [NetValue]*0.175 END
The problem is that the change is failing regression tests because of rounding differences - e.g. a result of 95.3191 becomes 95.3192. This otherwise insignificant difference means I can't simply demonstrate that the new code gives the same results. (I could develop a new regression test that ignores the last digit but then I'd have to test that test, and then ... no not going there :crazy:.)
What data type does SQL Server 2008 assume for the literal number in a calculation like (money * 1.175)? For my VAT_Rate variable, should I be using Decimal instead of Real? E.g. DECLARE @VAT_Rate Decimal(8,4)
May 31, 2011 at 7:34 pm
Let's stop and think about a calulator for a minute. Would you use one that only had a scale of 4 decimal points? Probably not. Would you use one that did it's calculations in binary and then returned it's answer in decimal knowing that certain decimal numbers have no binary equivalent within the scale of the calculator? Probably not.
For Multiplication and, certainly, Division on "money problems", do what any decent $3.00 calculator does behind the scenes... do the calculations in Decimal with at least 15 digits to the right of the Decimal Point and don't convert to a two Decimal Place number until you actually want to display it. For mortgage calculations, I'd never store just 2 Decimal Places either. Spreadsheets don't. They just display the answer with 2 Decimal Places unless you make the huge mathematical error of rounding such things.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 1, 2011 at 5:49 pm
SQL Server does a very strange stuff with precision of the result when doing basic math operations (*, /). I did a research and to put it short, use DECIMAL(25,13) type if you want to retain precision, or generally, DECIMAL(p,s) where p+s = 38!
REAL is not a precise type, it's a floating point type.
If you return money, be aware that you actually rounded the result to 4 decimal places.
So, you should probably go with something like this:
CREATE FUNCTION [dbo].VAT (
@Net_Amount [money],
@Order_Date [date] )
RETURNS money
AS
BEGIN
DECLARE @VAT_Rate DECIMAL(25,13) =
CASE
WHEN @Order_Date >= '2011-01-04' then 0.200
WHEN @Order_Date >= '2010-01-01' then 0.175
WHEN @Order_Date >= '2008-12-01' then 0.150
WHEN @Order_Date >= '1991-01-01' then 0.175
WHEN @Order_Date >= '1979-06-18' then 0.150
WHEN @Order_Date >= '1974-07-01' then 0.080
WHEN @Order_Date >= '1973-04-01' then 0.100
ELSE 0.000
END
RETURN CONVERT( DECIMAL(25,13), @Net_Amount ) * @VAT_Rate
END
GO
June 3, 2011 at 3:16 pm
For the rate, either DECIMAL(8,4) or DECIMAL(25,13) is good in this case; both give exactly the same results. But they still don't match the original test data - about 1 in 30 differ in the last digit.
At first it appeared that if I did the calculation in-line, the result was rounded, but if I did it in a function, it was truncated! I know there are issues with rounding vs. truncation - see this article - but the difference didn't make sense ... until I realised that some of the results were being multiplied by additional numbers. Here's some code that demonstrates the real problem:
IF OBJECT_ID (N'[dbo].TESTFunction', N'FN') IS NOT NULL DROP FUNCTION [dbo].[TESTFunction];
GO
CREATE FUNCTION [dbo].TESTFunction (@Gross_Amount [money])
RETURNS money
AS BEGIN RETURN @Gross_Amount/1.15 END
GO
DECLARE @gross [money] = 48.0000;
DECLARE @test1 [money] = (@Gross/1.15)*0.26*0.9; -- Sets 9.7670
DECLARE @Test2 [money] = dbo.TESTFunction(@Gross)*0.26*0.9; -- Sets 9.7669
SELECT @test1, @Test2;
Well, Vedran was right about the problem of the MONEY type. The function was rounding to 4 decimal places BEFORE the final calculation. When I changed the function's type to DECIMAL(25,13) (or even REAL), the differences with the original outputs disappeared.
BTW this is yet another example of a test that shows a problem that doesn't matter in the real application, where the result is only needed to the nearest penny anyway. But the regression testing was still worth doing - having fixed the small difference, I could then see and fix two genuine mistakes elsewhere in my code. And it was also a learning experience 🙂
June 4, 2011 at 3:53 am
Hi Joe. I am honoured to receive one of your rants 🙂
"Remember the early versions of UNIX?" - sorry, I don't know what you're getting at here. I never saw the very early UNIX - I first met it on an LSI-11 in 1979 (V6 I think), and then in 1981 became a user of 4.1BSD on a VAX. Are you referring to the splits between different versions I wonder? Or the fact that a UNIX file was a sequential stream of bytes without any specific record structure, aot VMS which had records and even key-based random access? Porting file handling between UNIX and VMS was indeed a pain - and even between the different versions of UNIX. (Especially when POSIX tried to standardise it and broke lots of existing code - remember the 14-character file name limit?)
Anyway, getting back to SQL, though I've previously made limited use of early Oracle, Tandem and Microsoft Jet SQL (all incompatible with each other), I have only recently started using Microsoft SQL Server and writing serious SQL for it, and have no real knowledge of its antecedents and archaeology. Reading Microsoft documentation, MONEY looked like what you used to represent money! It even gave you 2 extra digits to allow for rounding errors. Which I guess is why I should have used DECIMAL in my user functions to drive the rounding errors beyond the 4th digit...
Your 3rd point is an interesting one. We're using SSRS to do the reporting, so I haven't had to worry about what my Value columns look like when cast to strings, (other than in test output). But this also means I can't just change those columns' data types without causing problems for the reporting guy. But I can and now will use an appropriate precision of DECIMAL for internal calculations.
July 30, 2012 at 8:42 am
I had decimal(18,2) made it to more decimal points solved rounding problem.:-)(Thanks Jeff, Life Saver)
July 30, 2012 at 2:09 pm
Beside the type, I would add two more points.
If you want to be absolutely sure that you will retain the precision in your calculatins:
• use decimal(25,13) in internal calculation for ALL partcipants
• do operatins that enlarge the result first, and those that make result smaller do last. Eg do multiplicatins and additions first, and postpone substraction and divide operations to the end.
• if you have more than one operation, each operation result must be casted to decimal(25,13). Ugly, but that ensure you have no loss of precision.
For example, if you have three decimal(25,13) variables, instead of:
@x/@y*@z
Rewrite like this:
Convert(decimal(25,13), @x*@z)/@y
Notice different order of operations and cast of result even all the operans are decimal(25,13).
July 30, 2012 at 2:55 pm
Going slightly off-topic, you may also want to rewrite your function as an itvf and use it with CROSS APPLY in your FROM clause instead of using the scalar function in the SELECT list.
CREATE FUNCTION [dbo].VAT2 (
@Net_Amount DECIMAL(25,13),
@Order_Date [date] )
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN(
SELECT @Net_Amount *
CAST(CASE
WHEN @Order_Date >= '2011-01-04' then 0.200
WHEN @Order_Date >= '2010-01-01' then 0.175
WHEN @Order_Date >= '2008-12-01' then 0.150
WHEN @Order_Date >= '1991-01-01' then 0.175
WHEN @Order_Date >= '1979-06-18' then 0.150
WHEN @Order_Date >= '1974-07-01' then 0.080
WHEN @Order_Date >= '1973-04-01' then 0.100
ELSE 0.000
END as DECIMAL(25,13)) AS VatAmt)
GO
July 30, 2012 at 11:03 pm
Conficker (7/30/2012)
I had decimal(18,2) made it to more decimal points solved rounding problem.:-)(Thanks Jeff, Life Saver)
You bet. Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply