Round to Even (aka Banker''s Rounding) - The final function
-
I had started looking at that as well, but started finding some conversion issues between floats and varchar, then I lost all my work because my PC at home rebooted itself (auto updates, darn MS knows best!).
Did you also look at the Sergiy test using decimals?
-
I like it too. All the test examples from the original post work, as does a few of Sergiys numbers I've thrown in.
Now you just have to explain *how* it works, to a non mathmatician
Dave J
-
Dave J., What would work better is if you try a ell me what I am doing, and then I can fill in the gaps where things don't make since. A flow chart might help some also. I have a couple of tests I still need to run again after my walk to get lunch.
Lynn P.
-
Sergiy,
Here are some quick hints on English - no offense - please - it's meant to make us communicate better.
First you need to fix your comments.
This is better rendered as:
I don't quite agree with what you said...
Then I could take it into consideration.
Just leave that out. By answering my note, you already took it into consideration. In any case, it's not very friendly.
Here goes the rest:
2.0/3.0 is NOT naturally stored as 0.67
That depends who is doing the displaying and who receives what (this is meant to make you LOL). But I agree in principle with what you are saying here, i.e. there seems to be a tendency to store rounded down - see comments further below.
Run SELECT 2.0/3.0 and see how it's stored naturally.
This will NEVER TRULY tell you how it's stored naturally, especially with Query Analyzer - as I have demonstrated.
Seeing that you can realise that 0.55 would came back as 0.5000, not 0.5625.
Now this is an interesting point. I mean I'm no guru on low level representation of numbers. I would have thought that the software/firmware/hardware or whatever would pick the precise value that's CLOSEST to the value I TYPED IN. But it makes sense, i.e. always go for the lower storable/representable value.
Then, I like float numbers, as I like every other type of numbers. Probably a little bit more, because float numbers are the only numbers which display the true value stored in computer as it is.
Sergiy, I'm really surprised that you use the word 'true value'. I can't believe it. How can a computer display the 'true value' of something.
And the last major point. Your imprecision step is 0.0625. That means second digit is untrustful and rounding to second digit is incorrect mathematical operation.
I like your term 'imprecision step'. It makes a lot of things what you have said so far a lot clearer. No kidding.
Matter of fact, rounding to the first decimal digit is incorrect because it's gonna be based on the second digit you cannot trust.
I like this concept of some far off digit you cannot trust. Expand on that. Your use of coloquial 'matter of fact', 'gonna' is really cool.
The only rounding mathematically allowed here is ROUND(Value, 0).
This needs some clarification on your part. Although I feel that it's due to my 4 bit super computer squeezing you a bit. But I can see a general useful statement like that as being valid in this case.
In case of 0.55 ROUND() will receive value 0.5000 and will round it to 1.0000. What is right.
In case of 2/3 ROUND() will receive value 0.6250 and will round it to 1.0000. What is also right.
In case of 0.49 ROUND() will receive value 0.4375 and will round it to 0.0000. What is right as well.
You have certainly cleared up what Round() RECEIVES.
If you have finished with this (please make sure you understood and corrected all points) we can proceed further.
Let's proceed further. I think I did my part - hope you agree.
If you'll read my posts again, this time more attentive, you'll may notice that I did not rely on any "magic intelligence" embedded in the actual implementation of ROUND() function.
My conclusions were based on mathematical theory this method is based on.
Fortunately, the magic is still there.
As soon as 0.5000 is a representation for 0.55 (see the representation you've got for 2.0/3.0), and 0.0500 is as representation of 0.055 these numbers must be always rounded up.
No magic.
I can sense the magic in some of your statements. I'm just trying to bring it - and you - down to earth.
Simple logic.
Logic yes, simple NO.
-
Dave J.,
I completed the extra tests I needed, and I am still comfortable with what I wrote. I was commenting a copy of the code to help make it understandable, and then thought, I should ask you where you had problems understanding what I did so i could concentrate on that portion of the doc instead. Let me know so I can finish that part for you (and any one else who may want to know).
-
Wow!!!
You should immediately report a bug in SQL Server (and all other computer systems)!
Because SQL Server reports that 2.0/3.0 = 0.666666
If 0.666666 = 0.6666660000000000000 then 2.0/3.0 > 0.666666
SQL Server returns an error!!!
And all other computer systems as well!!!
I want to see how MS will figure out the way to answer your claim without using the word "idiot".
--------
For rounding to 6th digit read couple of pages ago.
_____________
Code for TallyGenerator -
This will NEVER TRULY tell you how it's stored naturally, especially with Query Analyzer - as I have demonstrated.
Demonstrated where? Cannot see any demonstration.
I would have thought that the software/firmware/hardware or whatever would pick the precise value that's CLOSEST to the value I TYPED IN.
Just not true.
It's not the way digital system work.
Even digital multimeter shows voltage not closest to the actual value but the level of last comparing when actual voltage was < comparator's internal voltage.
The only rounding mathematically allowed here is ROUND(Value, 0).
This needs some clarification on your part.
ROUND(Value, 1) is based on second decimal digit. Result of this function depends on the value of that digit.
Because your step is 0.0625 the second digit is being changed by 6 or 7 units at the time. So, you cannot trust this digit, and you cannot perform any operation taking this digit into consideration.
Actually you can, if you don't care about correctness of the result.
_____________
Code for TallyGenerator -
Sergiy,
What is this value: 0.66? Is it the 2 digit representation of 2/3? No, it is actually the amount of change I received at lunch this afternoon. It has NOTHING to do with 2/3's. When you use numbers without context, you can say we are wrong when we give you an answer you don't like. What is 0.666666? Is it the numeric representation of 2/3's or is it the measurement of a piece metal using a measuring device that is accurate to the 6 decimal digit? Again, you don't know, because I didn't put it into context.
You want us to give you good answers to your piss-poor questions when everything is given without any context to what it means. Not going to happen. You want accurate answers to questions, make sure there is meaning to the questions you ask.
And again, how about staying away from the name calling or is that all you are good at?
-
Hello Lynn,
Different time zones, and pressure at work makes 'real-time' communication difficult, but I will try to explain your function to myself in the next couple of days, and let you correct me where needed.
Thanks for your contribution, and most of the rest of the contributors too.
Dave J
-
Don't hesitate to contact me. It has been quite interesting to say the least. Hopefully, I may even find the opportunity to travel and take you up on visiting a pub. The Black Bull seems like a good place to go.
Later!
-
Yes.
you're right.
0.66 could bring you to different assumptions depending on the situation it was generated.
Again, you don't know, because I didn't put it into context.
Brilliant!
Finally you've got it!
YOU DON'T KNOW!
0.666666 can have any following digits.
It could be 0.66666612348, 0.666666000021, 0.66666698752, etc.
BECAUSE YOU DON'T KNOW!
As well as
0.045000 can have any following digits.
BECAUSE YOU DON'T KNOW!
That's why BR, assuming that all following digits MUST be zeros, is wrong by design.
BECAUSE YOU DON'T KNOW WHAT ARE THOSE DIGITS.
_____________
Code for TallyGenerator -
And still you don't get it either.
How do you know what is after 0.666666 in someone elses data base? Maybe that value is PRECISELY 0.666666 and if continued beyond 6 decimal positions actually was 0.6666660000..., it wasn't 0.66666612348 or any other set of numbers. Maybe the values that are being rounded using the Bankers Round Function to 2 decimal places had earlier been rounded to 4 decimal places by other processes using the traditional round function, making all decimal positions from the 5th onward all zeros.
You can not categorically call a process erronous and unusable using the logic you have been using in these threads. It all comes down to making business decisions based on business requirements. Nothing more, nothing less, and you can't change that.
-
Maybe.
Maybe not.
YOU DON'T KNOW BECAUSE YOU DON'T KNOW CONTEXT!
If the value has been rounded (does not matter in which way) that means you don't know what is the last digit. The fact you seeing "6" does not mean anything - it's rounded. Real digit in original value could be "5" or "6" - you don't know any more.
BR returns right result only when this sequence is (0).
And I proved (mathematically) that probability of such event is 0% - it's impossible.
So, in every case when BR returns result different from TR it's wrong. With 100% probability.
And I can categorically call a process erroneous.
It just returns wrong results.
> It all comes down to making business decisions based on business requirements.
Have nothing against business decisions.
It's not the first and (I'm sure) not the last wrong business decision I know about. Since it's not my money to be wasted I don't care.
Anyone who wants can use BR as intensively as they want.
But it does not change the fact that BR is wrong by definition.
_____________
Code for TallyGenerator -
Actually, Sergiy, you have proved nothing mathematically. We have yet to see any proof beyond simple numbers being thrown around by with no context or meaning behind them.
You go on believing what you believe, and the rest of us will do the same.
-
Your problem is you are "minor in math".
That's why you cannot understand mathematical proof.
Sorry.
But at least you understood "You don't know".
Not so bad for beginning.
Keep trying. Probably one day you'll get what math is about.
_____________
Code for TallyGenerator
Viewing 15 posts - 121 through 135 (of 377 total)
You must be logged in to reply to this topic. Login to reply