January 23, 2012 at 9:45 am
On a SQL 2008R2 / SQL 2008 / SQL 2005 (or for that matter almost any version)instance, can someone please explain this(if you can :w00t:):
set nocount on
declare @num1money
declare @num2money
declare @num3money
set @num1 = 2642.7100
set @num2 = 10739004.6800
set @num3 = 6694665.0000
select 'Calculated as literals, without variables : ', (2642.7100/10739004.6800)*6694665.000
SELECT 'Calculated with money variables(Wrong) : ', (@num1/@num2)*@num3
January 23, 2012 at 9:58 am
Because the MONEY data type only goes out to 4 decimal places. The extraneous value of 0.00004608518933991 is dropped by rounding.
SET nocount ON
DECLARE @num1MONEY
DECLARE @num2MONEY
DECLARE @num3MONEY
SET @num1 = 2642.7100
SET @num2 = 10739004.6800
SET @num3 = 6694665.0000
SELECT 'Calculated as literals, without variables : ', (2642.7100/10739004.6800)*6694665.000
SELECT 'Calculated with money variables(Wrong) : ', (@num1/@num2)*@num3
SELECT 'Difference : ',(2642.7100/10739004.6800)*6694665.000 - (@num1/@num2)*@num3
SELECT 'Value lost by truncation/rounding :',(2642.7100/10739004.6800)-(@num1/@num2)
SELECT 'Value lost by rounding * num3 = same difference :',((2642.7100/10739004.6800)-(@num1/@num2))*@num3
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 23, 2012 at 10:04 am
It's simple. Check out the following webpage on Precision, Scale, and Length.
Specifically it says that datatypes other than decimal have a fixed precision and scale. The scale for money is 4 decimal places. Since your numerator is much smaller than your denominator, the scale is insufficient for your calculations. So you get 0.0002 instead of 0.000246085189339911899544 as the result of your division.
It's usually a good idea to do all of your multiplications before doing your divisions. If you had followed this rule, you would have gotten the correct result (1647.4579) even with the money datatype.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 23, 2012 at 10:14 am
Jason,
Excellent !! and many thanks for the reply. Please look at the modified code below. According to the documentation for the formulas used in calcs involving fixed point precision numbers(ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/fbc9ad2c-0d3b-4e98-8fdd-4d912328e40a.htm) would suggest that the resultant precision/scale of the calc should be a decimal(38, 24)
set nocount on
DECLARE @num1MONEY
DECLARE @num2MONEY
DECLARE @num3MONEY
DECLARE @num4decimal(38, 24)
SET @num1 = 2642.7100
SET @num2 = 10739004.6800
SET @num3 = 6694665.0000
SELECT 'Calculated as literals, without variables : ', (2642.7100/10739004.6800)*6694665.000
SELECT 'Calculated with money variables(Wrong) : ', (@num1/@num2)*@num3
SELECT @num4 = (@num1/@num2)*@num3
SELECT 'Calculated with money variables(Wrong) and assigned to a decimal(38, 24) : ', @num4
January 23, 2012 at 10:16 am
Drew - many thanks for your reply. I have a follow up question posted. Would appreciate your insights on that.
January 23, 2012 at 10:24 am
No, common misconception.
The three data types involved in the calculation are money, hence the intermediate results of the calculation are also money, then right at the end (after all the precision has been lost), the result is cast to the decimal.
If you want the intermediate results to be that decimal, one or more of the inputs (not the output variable) has to be that data type or explicitly cast to that data type.
Same as this:
DECLARE @v1 INT = 10, @v2 INT = 3, @v3 NUMERIC(10,8)
SET @v3 = @v1/@v2
SELECT @v3
The result is 3.00000000, because the two variables involved in the division are integers, so integer division, 10/3 = 3, then that answer is cast to the numeric data type.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2012 at 11:24 am
I'm no finance guru, so could someone explain to me what real-world situations would require me to multiply/divide one currency value by another? Interest rates aren't currency... they're percentages. Also, conversion rates are decimals.When do I multiply, for example, $20.37 by $1.89?
SJT
January 23, 2012 at 11:42 am
SJTerrill (1/23/2012)
I'm no finance guru, so could someone explain to me what real-world situations would require me to multiply/divide one currency value by another?
Al says to his friends Bill and Chuck "It's Dave's birthday. Let's treat him to dinner." They go to dinner and the check arrives for $120.60. Bill says "It's $30.15 each." Al says "No, it's $40.20 each". Chuck needs to divide $120.60 by $30.15 and to divide $120.60 by $40.20 before he realizes that Bill didn't want to treat Dave but Al did.
January 23, 2012 at 12:09 pm
SJTerrill (1/23/2012)
I'm no finance guru, so could someone explain to me what real-world situations would require me to multiply/divide one currency value by another? Interest rates aren't currency... they're percentages. Also, conversion rates are decimals.When do I multiply, for example, $20.37 by $1.89?SJT
How about "pot odds" in poker? The simplest form is the amount of money required to call divided by the total amount if you do call (that is, the amount currently in the pot plus the amount to call).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 23, 2012 at 12:13 pm
Also, there's no requirement at all for a money data type to store currency. The name implies it does, but it can store any fixed precision number.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2012 at 12:50 pm
SJT -
In the world of fund accounting, I can assure you that such divisions are very commonplace. Its a little difficult to explain in a forum without adding a lot of circumstantial/contextual explanations. A simple example though, would be a percentage redemption which is the redemption amount divided by the amount of capital. However, this is not only a reported / stored field but also used as an intermediate step for several calculations, in which a higher precision will be desired. However, the point that I think the majority of the replies are alluding to is well taken. I believe that usage of appropriate data type and judicious casting is what is called for, but that is also easier said than done, especially when the range of possible values for those fields is very, very wide.
January 31, 2012 at 7:24 am
A simple example of when you'd need to divide currency by currency is to determine profit margin. How much did you sell it for divided by how much you paid for it minus 1.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply