Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculations using the Money data type Expand / Collapse
Author
Message
Posted Tuesday, May 31, 2011 6:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 3:20 PM
Points: 115, Visits: 747
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 .)

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)
Post #1117807
Posted Tuesday, May 31, 2011 7:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:32 PM
Points: 35,584, Visits: 32,174
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 "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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 T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1117821
Posted Wednesday, June 1, 2011 5:49 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
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



_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1118467
Posted Friday, June 3, 2011 3:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 3:20 PM
Points: 115, Visits: 747
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
Post #1119739
Posted Friday, June 3, 2011 7:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:29 PM
Points: 1,945, Visits: 3,121
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 Morgan-Kaufmann 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
Post #1119785
Posted Saturday, June 4, 2011 3:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 3:20 PM
Points: 115, Visits: 747
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.
Post #1119815
Posted Monday, July 30, 2012 8:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 5:25 AM
Points: 73, Visits: 379
I had decimal(18,2) made it to more decimal points solved rounding problem.(Thanks Jeff, Life Saver)
Post #1337279
Posted Monday, July 30, 2012 2:09 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
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
Post #1337533
Posted Monday, July 30, 2012 2:55 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 20,801, Visits: 32,730
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1337557
Posted Monday, July 30, 2012 11:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:32 PM
Points: 35,584, Visits: 32,174
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 "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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 T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1337669
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse