Solved Rounding To 16Dp Decimal (3 Different Answers) (1 Viewer)

[dalski]

Thanks, Doc I didn't post a very good link but the point I was trying to make is that on one of my bad e.g's of LET coercion I cannot see why SQL would convert to SINGLE/ DOUBLE when they are not recognized data-types in SQL Server. Access is not even in the equation atm but the point I was making is that it seems SQL can convert to decimal & single but I can't see why it would do that natively without Access being in the equation. This is a side topic mind, I have bigger problems in understanding the math in the basic same type DECIMAL(28,16); which is the main problem I face. I'm not even converting types, the columns in real-use are the correct types; so no cast is happening. The e.g.

if you're going to want a decimal results in t-sql make sure the inputs are decimal

Thanks Isaac, they are:

Var's are explicitly declared as the decimal to what appears to be the correct scale & depicted in my analysis of the rules. The point is it's being truncated/ rounded to 10dp as Sonic pointed out. But following the rules I cannot see where I am going wrong. In my simple mind my math appears correct. According to MSN's Rules it should display the result as pic'd from Windows Calc: as highlighted : Hopefully someone points me to my error, else if my math is correct the rules are incorrect; it's much more likely I'm wrong, but I cannot see where.

 

[cheekybuddha]

But following the rules I cannot see where I am going wrong.

From the docs:

The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it's reduced to 38, and the corresponding scale is reduced to try to prevent truncating the integral part of a result. In some cases such as multiplication or division, scale factor isn't reduced, to maintain decimal precision, although the overflow error can be raised.

BEGIN
    DECLARE
        @e1 AS DECIMAL (28,16) = 1,
        @e2 AS DECIMAL (28,16) = 1.95;
     
-- ...

28+16 = 44
44 > 38

This could likely be the cause.

See Philipp's second code suggestion in Post #15 (NoMoreWTF)

Your result is the same as DECIMAL(28,10) - try that and you will see your output.

 

[dalski]

Thanks Dave, I believe we need to use the formula's as per the operation to accurately asses the result's precision & scale as per #17. I need to accurately assess because I have lots of these to do.

 

[The_Doc_Man]

I cannot see why SQL would convert to SINGLE/ DOUBLE when they are not recognized data-types in SQL Server.

Ah, but they ARE, just under other names. REAL is the equivalent to SINGLE and FLOAT is the equivalent to DOUBLE.

float and real (Transact-SQL) - SQL Server

float and real (Transact-SQL)

learn.microsoft.com

A rose by any other name... would still stink so good! (And Will Shakespeare is probably turning over in his grave right now.)

 

[cheekybuddha]

I believe we need to use the formula's as per the operation to accurately asses the result's precision & scale as per #17.

Are you plotting inter-planetary flight paths? That's a lot of scale for such large numbers!

I'm not sure how you go about it.

As explained,

    DECLARE
        @e1 AS DECIMAL (28,16) = 1,
        @e2 AS DECIMAL (28,16) = 1.95;
       
    -- is the same as:

    DECLARE
        @e1 AS DECIMAL (28,10) = 1,
        @e2 AS DECIMAL (28,10) = 1.95;

Are the whole parts of the numbers you are dealing with really going to be larger than 999,999,999,999 ?

Will the following not suffice?

    DECLARE
        @e1 AS DECIMAL (22,16) = 1,
        @e2 AS DECIMAL (22,16) = 1.95;

 

[dalski]

Thanks, I need to pick limits to work with & they're going through several multipliers, being converted to percentages & multiplied against each other. So compounding has a massive effect very quickly after the decimal place & I'm trying to find what's going to be the best combination with these. I may be a little excessive with decimal(28,16) so i=12 (999,999,999,999) as you pointed out but it certainly needs to have an integral of > 9 & really as big as possible is best from a user-perspective. Regarding decimal places I need as much as I can get; test calc's done to 15 dp & inaccuracies noticed on very small amounts, so compounding will greatly exaggerate this.

I don't know yet & that's the point of this thread. I have a lot of work to do in deciphering these. Understanding the algebra behind the math is the topic; not the literals of a single e.g. But obv's I had to give an e.g. to apply the problem to. I'm very grateful of the help here obv's... So do not want this to sound argumentative, this is debate as to what the actual problem is. Forget meaninglessly reducing precision for a single e.g., it's about mastering this topic for the scores of storage capcities required for my app to function & LET coercion (later, struggling enough with the basics atm ).


@cheekybuddha I cannot see how #22 applies. You've picked rule 1 of the very first rules. Which seems to relate to addition/ subtraction more so. As my operation is division it ambiguously refers to 'scale factor'. Obviously I'm wrong somewhere in my logic but in trying to figure it all out:

But contradicts the the absolute value of 38 (p+s) is too simplistic a total for p here. We need to deduce the Result Total dependant to exactly what operation is in effect; that's division in this case. So Result Precision, Scale & the Integral must be calc'd as so.

Seems you have deduced a single expression only; where all expressions need to be assessed dependant on what operation is in effect; which has been done in #17 (division operation).

28+16 = 44
44 > 38

Then integral needs to be deduced.

Which again is dependant to if an addition/ subtraction is used OR a multiplication/ division is in effect. Which is dependant on the s required of the result; which is dependant whether it is +/- OR *//... I think I get the gist you're getting at:

So Result P = 73
Result S = 45
Multiplication & Division Rules apply; so M&D rule-3 applies; As the Result Integral (i) is > 32 ∴ s=6 & i=32; resulting in dec(38,26) so the nr returned would be truncated to s=6 (6 decimal places) & a 32 point integral. Yet we're not seeing that; we're seeing 10 decimal places:

I've gone step by step in #17... providing a detailed computation considering all expression terms... Hopefully someone can point out where I'm wrong with the algebra. Again not wanting to offend, but stating different variations of decimal type is of little help. The topic is more complex than that. Been some awesome input which I'm very grateful of, but as people keep point out 'need to accurately assess storage requirements...'. Is what is trying to be done in #17. Again hopefully someone can point out what my error is in my logic/ math.

 

[sonic8]

So Result P = 73
Result S = 45
Multiplication & Division Rules apply; so M&D rule-3 applies; As the Result Integral (i) is > 32

The integral part is: Precision - Scale.
In your case: 73 - 45 = 28
So, Rule #1 applies!
Scale = min(scale, 38 - (precision-scale))
In your case: min(45, 38-(73-45)) = 10

 

[dalski]

Thank you sonic, & thank you all for your help. I finally get it & never would have been able to get here without all your help. Thank you so much. To clarify Rule-1 of the Multiplication/ Division Rules. Not the very first rule 1.