

SSCEnthusiastic
Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:37 PM
Points: 115,
Visits: 746


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 >= '20110104' then 0.200 WHEN @Order_Date >= '20100101' then 0.175 WHEN @Order_Date >= '20081201' then 0.150 WHEN @Order_Date >= '19910101' then 0.175 WHEN @Order_Date >= '19790618' then 0.150 WHEN @Order_Date >= '19740701' then 0.080 WHEN @Order_Date >= '19730401' 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 .)
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)




SSCDedicated
Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 35,224,
Visits: 31,683


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 "RBAR is pronounced "reebar" and is a "Modenism" for "RowByAgonizingRow".
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
(play on words) "Just because you CAN do something in TSQL, doesn't mean you SHOULDN'T." 22 Aug 2013
Helpful Links: How to post code problems How to post performance problems




Old Hand
Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369,
Visits: 1,214


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 >= '20110104' then 0.200 WHEN @Order_Date >= '20100101' then 0.175 WHEN @Order_Date >= '20081201' then 0.150 WHEN @Order_Date >= '19910101' then 0.175 WHEN @Order_Date >= '19790618' then 0.150 WHEN @Order_Date >= '19740701' then 0.080 WHEN @Order_Date >= '19730401' then 0.100 ELSE 0.000 END RETURN CONVERT( DECIMAL(25,13), @Net_Amount ) * @VAT_Rate END GO
_____________________________________________________ Microsoft Certified Master: SQL Server 2008 XDetails Addin  for SQL Developers blog.sqlxdetails.com  Transaction log myths




SSCEnthusiastic
Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:37 PM
Points: 115,
Visits: 746


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 inline, 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 _{}




SSCommitted
Group: General Forum Members
Last Login: Friday, September 19, 2014 5:15 PM
Points: 1,945,
Visits: 3,008


1) It is proprietary, so porting it is a pain. It is one of the many "Sybase Code Museum" features from decades ago. Remember the early versions of UNIX?
2) Writing code in dialect when you don't need to make you sound like a hillbilly to people that speak the language. You are better off with DECIMAL(s,p) so you can use a properly sized column.
3) It does display and formatting in the back end, with commas and dollar signs. That defeats the purpose of a tiered architecture.
4) The MONEY data type has rounding errors.
Using more than one operation (multiplication or division) on money columns will produce severe rounding errors. A simple way to visualize money arithmetic is to place a ROUND() function calls after every operation. For example,
Amount = (Portion / total_amt) * gross_amt
can be rewritten using money arithmetic as:
Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)
Rounding to four decimal places might not seem an issue, until the numbers you are using are greater than 10,000. BEGIN DECLARE @gross_amt MONEY, @total_amt MONEY, @my_part MONEY, @money_result MONEY, @float_result FLOAT, @all_floats FLOAT;
SET @gross_amt = 55294.72; SET @total_amt = 7328.75; SET @my_part = 1793.33;
SET @money_result = (@my_part / @total_amt) * @gross_amt; SET @float_result = (@my_part / @total_amt) * @gross_amt; SET @Retult3 = (CAST(@my_part AS FLOAT) / CAST( @total_amt AS FLOAT)) * CAST(FLOAT, @gross_amtAS FLOAT);
SELECT @money_result, @float_result, @all_floats; END;
@money_result = 13525.09  incorrect @float_result = 13525.0885  incorrect @all_floats = 13530.5038673171  correct, with a 5.42 error
Books in Celko Series for MorganKaufmann Publishing Analytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQL SQL for Smarties SQL Programming Style SQL Puzzles and Answers Thinking in Sets Trees and Hierarchies in SQL




SSCEnthusiastic
Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:37 PM
Points: 115,
Visits: 746


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 LSI11 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 keybased 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 14character 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.




Valued Member
Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:20 AM
Points: 69,
Visits: 363


I had decimal(18,2) made it to more decimal points solved rounding problem.(Thanks Jeff, Life Saver)




Old Hand
Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369,
Visits: 1,214


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).
_____________________________________________________ Microsoft Certified Master: SQL Server 2008 XDetails Addin  for SQL Developers blog.sqlxdetails.com  Transaction log myths




SSCInsane
Group: General Forum Members
Last Login: Today @ 8:40 PM
Points: 20,687,
Visits: 32,295





SSCDedicated
Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 35,224,
Visits: 31,683


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 "RBAR is pronounced "reebar" and is a "Modenism" for "RowByAgonizingRow".
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
(play on words) "Just because you CAN do something in TSQL, doesn't mean you SHOULDN'T." 22 Aug 2013
Helpful Links: How to post code problems How to post performance problems



