CELKO (12/15/2010)
=================DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4);
SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000
SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3
SELECT @mon4 AS moneyresult,
@num4 AS numericresult
Output: 2949.0000 2949.8525
The problem here isn't necessarily in the money type, but it's in the override that occurs.
In particular, @Num1/@Num2 turns into either a float, or a decimal (20, 20)... leading me to assume the float.
Expanding on your code above:
DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4);
SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000
SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3
SELECT @mon4 AS moneyresult,
@num4 AS numericresult
select @mon1, @num1
select @mon2, @num2
select @mon3, @num3
SELECT @mon1/@mon2, @Num1/@Num2
SELECT CONVERT( DECIMAL( 19, 4), @Num1) / CONVERT( DECIMAL( 19, 4), @Num2)
SELECT CONVERT( DECIMAL( 19, 4), @Num1 / @Num2)
SELECT @Mon2*@Mon3, @Num2*@Mon3
SELECT 0.2949*10000, 0.2949852507374631268 * 10000
In particular, the results from SELECT @mon1/@mon2, @Num1/@Num2 are incredibly disparate, which is causing the difference.
The reconversion from the float into the correct # of significant digits also changes the resultant rounding, a known issue with float. It depends on what level of accuracy to what significance you want.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA