Declare @a Decimal (38,20) = 131134.848
declare @b Decimal (38,20) = 2240000
select @a / @b
Result 0.058542
while the correct value is :0.05854234285714290
Declare @a Decimal (38,20) = 131134.848
declare @b Decimal (38,20) = 2240000
select @a / @b
Result 0.058542
while the correct value is :0.05854234285714290
In this link, it is explained:
Especially part 3 gives the reason why you'd end up with 6 digits after decimal point.
declare @b Decimal (38,20) = 2240000 -- this is Quantity
Declare @a Decimal (38,20) = 131134.848 --- this is Value
Declare @c Decimal (38,20) = @a / @b
I want to spend this amount so that the value is 0
Taking into account that when drainage through the equation
@b * @c
Have you read and understood the section I posted?
If you declare your variables as decimal(32,20) you'd probably get what you want. decimal(32,20) can still hold a might big number/amount.
I understand
Declare @a Decimal (32,20) = 131134.848
declare @b Decimal (32,20) = 2240000
DECLARE @C DECIMAL (32,20)
SET @C = @a / @b
SELECT (@C * @b) - @a -- I need Result = 0
Then don't use Decimal (38,20) or CAST it in some way such that the calculation rules THEN suit your problem / purpose.
Do you really need numbers up to a trillion with 20 decimal places (its entirely possible that you do, just seems like a rare requirement ...)
I really hate that SQL Server does this... it makes Granny's 4 function calculator look great by comparison.
Because of the lousy MS rules that @bitsmed was good enough to post, you can avoid most forms of rounding hell by being careful not to scale your inputs to what you expect for an output. For example, the following provides an even more correct answer that what you provided as the "correct" answer.
DECLARE @a DECIMAL(19,3) = 131134.848
DECLARE @b DECIMAL(19,3) = 2240000
SELECT @a / @b
0.0585423428571428571 --Result from above
0.05854234285714290 -- What you posted as the "correct" answer, which still isn't rounded correctly.