November 1, 2010 at 5:14 am
I have this code that calculates a percentage:
SELECT (100 / CAST(a.MonthPop AS decimal(10, 2))) * (CAST(b.MonthPop
AS decimal(10, 2)) - CAST(a.MonthPop AS decimal(10, 2))) AS PercentChange
FROM popthis AS a INNER JOIN
poplast AS b ON a.[Pct Code] = b.[Pct Code]
go
...the result is weirdly out by a very small fraction. e.g 299.99999999%
instead of 300% Do you know how i'd sort this?
Andy
November 1, 2010 at 10:47 am
Could you wrap the function in a ROUND to get back to 300? For example:
SELECT ROUND( (100 / CAST(a.MonthPop AS decimal(10, 2))) * (CAST(b.MonthPop
AS decimal(10, 2)) - CAST(a.MonthPop AS decimal(10, 2))),2) AS PercentChange
November 1, 2010 at 10:52 am
The casts may be the cause, but can't tell without smple data.
What are the values for MonthPop in each of the tables?
November 1, 2010 at 12:12 pm
For best accuracy, it is usually better to have all multiplication before all division operations in any language.
select
round(
( 100.00 * (CAST(b.MonthPop AS decimal(10, 2))-CAST(a.MonthPop AS decimal(10, 2)) ) )
/
CAST(a.MonthPop AS decimal(10, 2))
,2)
as PercentChange
frompopthis as a
inner join
poplast as b
on a.[Pct Code] = b.[Pct Code]
November 1, 2010 at 2:38 pm
The results you are getting are documented in Books OnLine under the topics titled "int, bigint, smallint, and tinyint" and "Precision, Scale, and Length"
the result is weirdly out by a very small fraction. e.g 299.99999999% instead of 300% Do you know how i'd sort this?
On the final results, suggest you perform either a cast or use the round function.
"int, bigint, smallint, and tinyint"
When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, the rules that SQL Server applies when it calculates the data type and precision of the expression results differ depending on whether the query is autoparameterized or not.
Therefore, similar expressions in queries can sometimes produce different results. When a query is not autoparameterized, the constant value is first converted to numeric, whose precision is just large enough to hold the value of the constant, before converting to the specified data type. For example, the constant value 1 is converted to numeric (1, 0), and the constant value 250 is converted to numeric (3, 0).
Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number.
Operation Result precision Result scale
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
So, here are the precisions and scales for the calculations:
DECIMAL (3, 0) = the constant 100
DECIMAL(16,11) for (100 / CAST(a.MonthPop AS decimal(10, 2)))
DECIMAL(11,02) for (CAST(b.MonthPop AS decimal(10, 2)) - CAST(a.MonthPop AS decimal(10, 2)))
DECIMAL(28,13) for the final calculation.
SQL = Scarcely Qualifies as a Language
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply