Log in  ::  Register  ::  Not logged in

## Calculations using the Money data type

 Author Message David Data SSCommitted Group: General Forum Members Points: 1791 Visits: 829 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 moneyAS 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 ); ENDGO`(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 ENDThe 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) Jeff Moden SSC Guru Group: General Forum Members Points: 504863 Visits: 44238 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Vedran Kesegic SSCertifiable Group: General Forum Members Points: 5018 Visits: 1266 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 moneyAS 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 ENDGO` _____________________________________________________Microsoft Certified Master: SQL Server 2008XDetails Addin - for SQL Developersblog.sqlxdetails.com - Transaction log myths David Data SSCommitted Group: General Forum Members Points: 1791 Visits: 829 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];GOCREATE FUNCTION [dbo].TESTFunction (@Gross_Amount [money]) RETURNS moneyAS BEGIN RETURN @Gross_Amount/1.15 ENDGODECLARE @Gross [money] = 48.0000;DECLARE @Test1 [money] = (@Gross/1.15)*0.26*0.9; -- Sets 9.7670DECLARE @Test2 [money] = dbo.TESTFunction(@Gross)*0.26*0.9; -- Sets 9.7669SELECT @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 :-) David Data SSCommitted Group: General Forum Members Points: 1791 Visits: 829 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. Conficker Say Hey Kid Group: General Forum Members Points: 687 Visits: 386 I had decimal(18,2) made it to more decimal points solved rounding problem.:-)(Thanks Jeff, Life Saver) Vedran Kesegic SSCertifiable Group: General Forum Members Points: 5018 Visits: 1266 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*@zRewrite like this:Convert(decimal(25,13), @x*@z)/@yNotice different order of operations and cast of result even all the operans are decimal(25,13). _____________________________________________________Microsoft Certified Master: SQL Server 2008XDetails Addin - for SQL Developersblog.sqlxdetails.com - Transaction log myths Lynn Pettis SSC Guru Group: General Forum Members Points: 222745 Visits: 40382 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 SCHEMABINDINGAS 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 PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Jeff Moden SSC Guru Group: General Forum Members Points: 504863 Visits: 44238 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs