SQLServerCentral / SQL Server 7,2000 / T-SQL / Round to Even (aka Banker''s Rounding) - The final function / Latest Posts

RE: Round to Even (aka Banker''s Rounding) - The final function

Bob Bobbity — Thu, 21 Feb 2013 08:04:31 GMT

UrrghI have just identified the issue as being with my function, and one that I corrected elsewhere some time ago. Please ignore.For reference, below is the function being used, which handles the two cases posted on the first page of this thread as well as our regular requirement (rounding to 5 places)[code="sql"]CREATE FUNCTION dbo.RoundBanker (@val decimal(38,12), @pos int)RETURNS decimal(38,12)as -- Takes two parameters. First is number to be rounded, second is how many places to round to---------------------------------------------------------------------------------------------begin declare @tmpval1 bigint, @tmpval2 decimal(38,12), @retval decimal(38,12), @tmpval3 decimal(38,12), @tmpval4 decimal(38,12), @predec decimal(38,12), @postdec decimal(38,12) -- To get most from function, ignore everything before decimal point -------------------------------------------------------------------- set @predec = floor(abs(@val)) set @postdec = case sign(@val) when 1 then @val - @predec else @val + @predec end -- Actual work (Lynn's) ---------------------- set @tmpval1 = floor(abs(@postdec) * power(cast(10 as float), @pos)) set @tmpval2 = round(@postdec, @pos, 1) set @tmpval3 = sign(@postdec) * (0.5 * power(cast(10 as float), (-1 * @pos))) set @tmpval4 = (@postdec - @tmpval2) set @retval = round(@postdec, @pos, case when nullif(@tmpval1, (@tmpval1 / 2) * 2) is null and ((@tmpval3 >= @tmpval4 and sign(@val) = 1) or (@tmpval4 >= @tmpval3 and sign(@val) = -1)) then 1 else 0 end) -- Rebuild number ----------------- select @retval = case sign(@val) when 1 then @retval + @predec else 0.0 - @predec + @retval end return @retvalend [/code]

RE: Round to Even (aka Banker''s Rounding) - The final function

Bob Bobbity — Thu, 21 Feb 2013 07:22:18 GMT

Two questions please, having ploughed my way through this lesson in trolling:1. Was there a final version of the banker's rounding algorithm which rounded correctly to 5 decimal places any "representation of a number" (to avoid concerns over imprecise representations of numbers) stored in a decimal(38,12) field? I have a real-world need, where one of our systems connects with another which is doing this rounding, and the SQL database is not.I have an algorithm already (based I believe on one of those in this thread) which uses the power() function, and works fine except that this breaks down once the data being rounded is greater than 92,233,720,368,547.8 (rounded up from .746something), which is obviously less than the maximum possible in the decimal field. The example that I am failing on is 99999999999999.123565 to 5 decimal places, which should of course be 99999999999999.123562. We have clients based in New Zealand. Will they be arrested?

RE: Round to Even (aka Banker''''s Rounding) - The final function

Suresh Rao — Mon, 16 Jul 2007 05:23:00 GMT

I have not found any function in SQL Server 2005 to peform bankers rounding.

You do have one in the Dot Net Framework

Math.Round Method (Decimal, MidpointRounding)

http://msdn2.microsoft.com/en-us/library/ms131274.aspx

If you are using SSRS you can use framework functions in the Report Designer.

One work around that worked for me is to increase the precision to 3-4 digits more than required ( I needed 6 , the i used 10 digits ) and then round the result. This does not work if the summation is for thousands of rows.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Lynn Pettis — Sun, 15 Jul 2007 22:40:00 GMT

Sergiy,

Its do bad your only arguments are attacks on other peoples education, experience, and beliefs. You would do better to provide specific facts and citiations to support your arguments rather than the attacks you make on people. They are constant, demeaning, and do nothing to support your arguments.

Why not try being constructive and supportive instead of these continuous attacks. Of course, the answer is simple; you are always right even when you have been proved wrong with specific facts and tests that you yourself purposed but never accepted.

I think we all finally learn when to stop banging our heads against the brick wall.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Sun, 15 Jul 2007 15:54:00 GMT

> even advanced mathematics is based on elementary (not school) principles.Probably.But elementary mathematics does not operate with such thing as infinity.That's you don't know how to deal with it. > And Achilles will over take the tortoise; its physics, not philosphy.Yes, you can witness it.And author of this problem knew Achilles will over take the tortoise. That's why he named it "paradox".But you're too dull to solve this scientific problem. You end up "extremely small" distance between Achilles and the tortoise which never becomes zero. You believe in "absolute precise" numbers, and zero as something imprecise like 1/infinity does not fit your religion.Sorry for you.BTW, in ancient Greece they used name "philosophy" for all science. It's another thing you missed in school.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Lynn Pettis — Fri, 13 Jul 2007 23:48:00 GMT

Actually, I am quite aware of the great range of mathematics, but even advanced mathematics is based on elementary (not school) principles.

And Achilles will over take the tortoise; its physics, not philosphy.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Fri, 13 Jul 2007 23:21:00 GMT

1 > undefined = undefined1 > infinity = falseNow you tell me if what they are saying at this link is true.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Fri, 13 Jul 2007 22:55:00 GMT

Really?Distanse between them will become smaller and smaller on every approach.Until it will become "extremely small", "extremely close" to zero.But it's not zero, as you stated.So, who is right?

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Fri, 13 Jul 2007 22:52:00 GMT

Lynn, believe me, there is a whole world of math beyond the level of primary school arithmetics.You just don't know about it, but it's there.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Lynn Pettis — Fri, 13 Jul 2007 22:43:00 GMT

Perhaps you should tell me if what they are saying at this link is true:

http://mathforum.org/dr.math/faq/faq.divideby0.html

RE: Round to Even (aka Banker''''s Rounding) - The final function

Lynn Pettis — Fri, 13 Jul 2007 22:41:00 GMT

No, Achilles will over take the tortoise. But even then, you will never be wrong, right?

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Fri, 13 Jul 2007 22:31:00 GMT

Lynn, you're so bad.You cannot even get how bad and low you are.Don't tell anybody that n/0 = undefined, not infinity.Because it's a shame to be so ignorant as you are.For those who did not achieve in school and believes is "extremely close" things:http://archimedes.mpiwg-berlin.mpg.de/cgi-bin/archim/dict/hw?lemma=ACHILLES&step=entry&id=d006Distance between Achilles and the tortoise will be extremely small, but never zero.Right?

RE: Round to Even (aka Banker''''s Rounding) - The final function

Lynn Pettis — Fri, 13 Jul 2007 22:23:00 GMT

And again you are wrong. n/0 = undefined, not infinity. But then again, I'm sorry, you are never wrong, are you?

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Fri, 13 Jul 2007 22:09:00 GMT

Not really.Infinity is not undefined.0* infinity depends on the way infinity was received.If infinity came from 5/0 then 0*infinity = 5If infinity came from 1/0 then 0*infinity = 1If infinity came from 5/0/0 then 0*infinity = infinityOf course, if you don't know which way infinity was built, it becomes NULL.Fortunately, there is no way to store infinity in computers, so it's not a problem.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Jeff Moden — Fri, 13 Jul 2007 21:52:00 GMT

Heh... or...

Infinity = undefinedNull = undefined

1/Infinity = undefined?

1/Infinity = Null?

Just havin' fun

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Fri, 13 Jul 2007 21:49:00 GMT

Infinity is not any number.0*infinity is not 0.6th grade math.Theory of limits.Go and study.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Lynn Pettis — Fri, 13 Jul 2007 21:04:00 GMT

Wrong!

If 1/infinity = 0 then 1 = 0 * infinity which is wrong, as any grade school student will tell you that 0 * any number (0 * n ) = 0. Therefore 1/infinity can not equal zero. It can come infinitly close, but it will never BE 0.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Fri, 13 Jul 2007 19:52:00 GMT

1/infinity = 0It's not extremely close, it's infinitely close.There is nothing more than infinity.As I said, you missed some very important classes in school.P.S. Remind everybody, you are ignoring whom?

RE: Round to Even (aka Banker''''s Rounding) - The final function

Lynn Pettis — Fri, 13 Jul 2007 19:46:00 GMT

Just one objection Sergiy, 1/infinity <> 0. It is extremely close, but isn't, so there is a possibility however slight that you can get a precise value.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Fri, 13 Jul 2007 17:47:00 GMT

What is "12.5" value?Where you've got it from?It's a result of either some measurement or some calculation (of values measured) stored with some precision you cannot change and have to accept.So, if "12.5" stored in money datatipe field tells you that the real value is between 12.5000(0) and 12.5000(9).Storing it in DECIMAL(3, 1) field you decrease its precision, because money value 12.5123 will end up in exactly the same DECIMAL(3, 1) value as 12.5421 or 12.5252.So, you cannot use assumption of "implied zeros" as some "brilliant minds" suggested here. > Are you saying that some numeric values cannot be stored precisely, or are you saying that *all* numeric values cannot be stored precisely? You probebly did not read the whole topic. Because I answered on this question at least 3 times.OK, it's Saturday here, so I can afford to repeat it again and not to force you to go through this.There is 1 value which is represented by 12.5000 precisely: it's 12.5000(0).There is infinite number of values which are represented by 12.5000 not precisely: > 12.5000(0) and < 12.5001(0).The probability of the actual value (which don't have a chance to know) is represented by 12.5000 precisely equals 1/infinity = 0."Probablity = 0" means "it's impossible".If it's impossible event when numeric value is stored precisely then all numeric values cannot be stored precisely.Any objection to this logic?

RE: Round to Even (aka Banker''''s Rounding) - The final function

dmbaker — Fri, 13 Jul 2007 06:57:00 GMT

Try storing the value "12.5" in a DECIMAL(3, 1) field.

I already said that I understand perfectly well that there are some numeric values that cannot be stored precisley in SQL Server (and it's not just decimal/floating point values). As such, performing your excercise is not necessary and is irrelevant to my underlying question (that being, how do I resolve what you said with what Books Online says, as Books Online apparently contradicts you).

Are you saying that some numeric values cannot be stored precisely, or are you saying that *all* numeric values cannot be stored precisely? Maybe if you would be so kind as to answer that question, I'll have a better understanding.

On a related note, can you tell me why manufacturers of toasters put a setting on their toasters that inevitably chars the toast beyond all hope of recovery? Are there people out there who really like to eat chunks of carbon?

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Fri, 13 Jul 2007 06:29:00 GMT

Repeat it 7 times before you go to bed.It will probably meka you feel happy.But don't do it on public.You are ridiculous enough even without it.

RE: Round to Even (aka Banker''''s Rounding) - The final function

David Burrows — Fri, 13 Jul 2007 02:51:00 GMT

Do your homework first, then join the discussion.

You answer my questions first

But I know that you cannot

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Thu, 12 Jul 2007 18:22:00 GMT

Do your homework first, then join the discussion.

RE: Round to Even (aka Banker''''s Rounding) - The final function

David Burrows — Thu, 12 Jul 2007 18:15:00 GMT

David, have you done your homework with one thirds?

What has that to do with the questions I asked?

Are you unable to answer a straightforward question?

It seems not, as you have not answered any questions asked of you, because you are unable to.

You only obfuscate but have yet to provide any answers or proof, because again you cannot.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Thu, 12 Jul 2007 17:50:00 GMT

David, have you done your homework with one thirds?

RE: Round to Even (aka Banker''''s Rounding) - The final function

David Burrows — Thu, 12 Jul 2007 16:19:00 GMT

Absolutely precise numbers must have p = infinity.

Sergiy, please define your interpretation of "precise number"

Please post a reference that proves this statement.

absolute precision

Again, please define your interpretation of "absolute precision"

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Thu, 12 Jul 2007 16:13:00 GMT

Try to store 1/3 in DECIMAL(12,6) and DECIMAL(12,4)Find the difference and see who really gives a crap.

RE: Round to Even (aka Banker''''s Rounding) - The final function

dmbaker — Thu, 12 Jul 2007 16:06:00 GMT

Yeah, sorry for the multiple posts, not sure why that happened. Anyway, I'm still not clear on what you're talking about.

Is it perhaps that you're playing a semantic game whereby you contend that the number is not its representation? If that's the case then I have to ask "So what? Who really gives a crap?"

And "who really gives a crap" I think, pretty much sums up this whole argument, doesn't it. ")

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Thu, 12 Jul 2007 15:49:00 GMT

dmbaker,I wish you read it as many times as you posted it.an exact representation of the numberRepresentation is named exact, not the number.I really don't know what they mean by naming it "exact" but it could not be absolute precision, because on the same page they specify the limitation for maximum possible precision.

RE: Round to Even (aka Banker''''s Rounding) - The final function

dmbaker — Thu, 12 Jul 2007 14:44:00 GMT

Yeah, OK, whatever. Would you please, as I originally asked, elaborate on the apparent contradiction between your statement:

No datatype decribed as holding absolute precise numbers. Because it would be insane.

And what Books Online says:

The decimal data type stores an exact representation of the number; there is no approximation of the stored value.

I think the disconnect is what you mean by "precise" and what SQL Server means by "exact representation". To me, they mean the same thing, but to you they apparently do not. Would like to understand more why that is and what you base it on (e.g. do you have any citations that I may refer to, as opposed to anecdotal sources).

RE: Round to Even (aka Banker''''s Rounding) - The final function

dmbaker — Thu, 12 Jul 2007 14:44:00 GMT

Yeah, OK, whatever. Would you please, as I originally asked, elaborate on the apparent contradiction between your statement:

No datatype decribed as holding absolute precise numbers. Because it would be insane.

And what Books Online says:

The decimal data type stores an exact representation of the number; there is no approximation of the stored value.

RE: Round to Even (aka Banker''''s Rounding) - The final function

dmbaker — Thu, 12 Jul 2007 14:44:00 GMT

Yeah, OK, whatever. Would you please, as I originally asked, elaborate on the apparent contradiction between your statement:

No datatype decribed as holding absolute precise numbers. Because it would be insane.

And what Books Online says:

The decimal data type stores an exact representation of the number; there is no approximation of the stored value.

RE: Round to Even (aka Banker''''s Rounding) - The final function

dmbaker — Thu, 12 Jul 2007 14:44:00 GMT

Yeah, OK, whatever. Would you please, as I originally asked, elaborate on the apparent contradiction between your statement:

No datatype decribed as holding absolute precise numbers. Because it would be insane.

And what Books Online says:

The decimal data type stores an exact representation of the number; there is no approximation of the stored value.

RE: Round to Even (aka Banker''''s Rounding) - The final function

dmbaker — Thu, 12 Jul 2007 14:44:00 GMT

Yeah, OK, whatever. Would you please, as I originally asked, elaborate on the apparent contradiction between your statement:

No datatype decribed as holding absolute precise numbers. Because it would be insane.

And what Books Online says:

The decimal data type stores an exact representation of the number; there is no approximation of the stored value.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Sergiy — Thu, 12 Jul 2007 14:24:00 GMT

dmbaker,in definition of datatype "DECIMAL (s, p)" what is "p"?What is the range of possible values of "p"?Absolutely precise numbers must have p = infinity. Can you specify such datatype?

RE: Round to Even (aka Banker''''s Rounding) - The final function

dmbaker — Thu, 12 Jul 2007 11:21:00 GMT

I realize what the original discussion is about and I have no particular concerns there. What concerns me is the disconnect between what Books Online says and what Sergiy said, given as how he said to look in Books Online.

I "opened BOL and read about datatypes". SQL Server is perfectly capable of storing precise representations of decimal values, so it does not seem very accurate to say "No datatype described as holding absolute precise numbers", if we assume Books Online is the authority in this case.

It would probably be more accurate to say "no datatype in SQL Server is capable of representing every possible numeric value with perfect precision". 1/3 is a good example of that. If this was the intent then I apologize for misunderstanding.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Michael Meierruth — Thu, 12 Jul 2007 10:55:00 GMT

No one is denying or questioning that you can store a precise value, say 1.25 followed by an infinte number of zeroes, into a SQL Server decimal data type that can handle up to 38 digits (before or after the decimal point). What is being discussed is that the BR rounding function is applied to values that are calculated and most of which cannot be represented precisely in any computer data type.Thus if my boss comes to me and says that he will decrease my salary by one third and even threatens me that he will apply BR rounding on the decreased value, will I faint because he decreased my salary by one third or because he applied BR rounding? Even if I'm a super rich philanthropist officially earning 1 dollar a year (oops 1 euro a year), the decrease in salary wouldn't matter much.Of course, we all agree that my boss is not very wise to make this kind of threat (notice, I didn't use the word stupid).

RE: Round to Even (aka Banker''''s Rounding) - The final function

dmbaker — Thu, 12 Jul 2007 07:11:00 GMT

Regarding "No datatype described as holding absolute precise numbers...", this quote from Books Online seems to contradict you:

Using decimal, float, and real Data

The decimal data type can store a maximum of 38 digits, all of which can be to the right of the decimal point. The decimal data type stores an exact representation of the number; there is no approximation of the stored value.

Can you please elaborate on this apparent contradiction? Note please that it is not my intent to attack you, I'm simply trying to understand what appears to me to be an inconsistency. I'm fully able to accept that SQL Server is insane, not necessarily based on this one occurance but through my experience with other aspects of it.

RE: Round to Even (aka Banker''''s Rounding) - The final function

Michael Meierruth — Wed, 11 Jul 2007 14:46:00 GMT

This will be my last post - on this thread.It may be my last post on this forum - no threat implied.I thus invite everyonewho wants or needs to say one last thingto do so nowor forever hold your peace.May I suggest it be something - that does not incite.May I suggest it be something - that is positive.May I suggest it be something - that is constructive.May I suggest it be something - that is nice.Believe me - it may not be easy!Look deep inside you.Believe me - it's there.If not - take another look.No justifications - please!You found it!Peace.

Please.