Round to Even (aka Banker''s Rounding) - The final function
-
And the winner is... drums rolling...
or should I say heads rolling:My vote goes for Sergiy. By the way, David Burrows's code gets it wrong with 2.265001.
-
By the way, David Burrows's code gets it wrong with 2.265001 Yep
I missed the bit in the spec about trailing zeroes
I don't believe a solution is possible using either real or decimal data type for input due to possible unwanted implicit rounding/conversion. The only way for the function to work correctly is for it to have varchar input and leave any initial rounding/data issues to the calling proc.
Far away is close at hand in the images of elsewhere.
Anon. -
I agree that when you have a number in varchar format you can do BR perfectly using just string manipulation, i.e. finally we are back to WYSIWYG.
But most of the numbers are already in some numeric data type and are thus already possibly ruined. And when you convert these ruined numeric data types to varchar you get just a ruined varchar number.
Using one of Sergiy's favorite numbers I ran the following in Query Analyzer
select cast(50.0 as float(53))/cast(111.111 as float(53))
select cast(cast(50.0 as float(53))/cast(111.111 as float(53)) as varchar(30))
declare @n float(53)
set @n=50.0/111.111
select @n
select cast(@n as varchar(30))
which returns
0.45000045000044997
0.45
0.45000045
0.45
So who are we going to believe?
And when your favorite (and better) conversion routine from numeric to varchar results in 0.65000000000000001 what will your BR routine do with that last digit?
-
Any method you choose will produce some form of 'ruined' value. My suggestion to use varchar as input to the function will remove any ambiguity within the function itself and it can apply the rules correctly and accurately.
Since some form of conversion or rounding takes place within sql server then, in my opnion, it is best done by the calling system where it can be controlled correctly depending on the data itself.
As a footnote, I also think that the addition of 1 for non trailing zeroes is ambiguous in itself.
Far away is close at hand in the images of elsewhere.
Anon. -
If you think that last digit is ambiguous try the following:
declare @n float(53)
set @n=0.65
select @n
set @n=0.65000000000000001
select @n
set @n=0.65000000000000002
select @n
set @n=0.65000000000000003
select @n
set @n=0.65000000000000009
select @n
This will get you:
0.65000000000000002
0.65000000000000002
0.65000000000000002
0.65000000000000002
0.65000000000000013
Very ambiguous indeed.
I think maybe that's what Sergiy was trying to explain.
-
Sergiy claims that the concept of BR is crap, even if perfectly implemented. That's where most of us have a beef with him.
While we all agree that the BR implementation in the beginning of this thread suffers from the floating point issues inherent in SQL Server, when it was pointed out to him that Round() behaves the exact same way (i.e., I can pass it a number that when converted to float changes the result of the round), he defended the Round() function, putting the blame on SQL Server's (and computers in general) handling of floating point numbers. He doesn't give the BR function the same slack.
In other words, the BR function has the exact same flaw as the Round() function, in that it processes what it receives, yet somehow BR is bad, and Round() is good. .
While I agree that BR implementation in SQL Server isn't necessarily perfect (I'm working on a decimal version as we speak), I don't agree that the concept of BR is bogus.
-
if you go to this link, it seems to suggest that 64 Bit servers don't suffer from this. Any one got a 64 bit SQL implementation handy?
I tried 612.945
Dave J
-
I have a 64-bit server here at work, what is the final code?
-
Would someone like to test the following and let me know how it works?
ALTER FUNCTION [dbo].[fn_BRound] (
@val decimal(38,20),
@pos int
)
RETURNS decimal(38,20)
as begin
declare @tmpval1 int,
@tmpval2 decimal(32,20),
@retval decimal(32,20)
set @tmpval1 = floor(@val * power(cast(10 as float), @pos))
set @tmpval2 = round(@val, @pos, 1)
set @retval = round(@val, @pos, case
when nullif(@tmpval1, (@tmpval1 / 2) * 2) is null
and (0.5 * power(cast(10 as float), (-1 * @pos)) >= @val - @tmpval2)
then 1
else 0 end)
return @retval
end
-
I claim that concept is crap and therefore there is no perfect implementation.
If thee would be perfect implementation you would post it.
Flooding topic by you with words instead of single working implementation proves I'm right.
> In other words, the BR function has the exact same flaw as the Round() function
Not true.
Round() does not use precise numbers. It takes the value supplied as a representation of all values between this value and next value bigger than this by step of precision.
That's why there is no problem to supply 612.945
It's possible in any programming language on any, even 8-bit, computer.
Assumption of absolute precision of supplied values makes it impossible.
On any, even 64-bit, computer.
Because increasing depth of precision does not cancel the fact that (David, see the link you referenced) fractional decimal numbers cannot be precisely represented in binary format.
And when you're being fooled with numbers you see on screen rewmind yourseld beginner's question: "In which format datetime values are stored in database?"
_____________
Code for TallyGenerator -
Made a couple of mods, and here are my changes:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_BRound] (
@val decimal(38,20),
@pos int
)
RETURNS decimal(38,20)
as begin
declare @tmpval1 int,
@tmpval2 decimal(38,20),
@retval decimal(38,20),
@tmpval3 decimal(38,20),
@tmpval4 decimal(38,20)
set @tmpval1 = floor(@val * power(cast(10 as float), @pos))
set @tmpval2 = round(@val, @pos, 1)
set @tmpval3 = (0.5 * power(cast(10 as float), (-1 * @pos)))
set @tmpval4 = @val - @tmpval2
set @retval = round(@val, @pos, case
when nullif(@tmpval1, (@tmpval1 / 2) * 2) is null
and @tmpval3 >= @tmpval4
then 1
else 0 end)
return @retval
end
GO
/****** Object: UserDefinedFunction [dbo].[fn_BRound2] Script Date: 06/18/2007 15:26:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_BRound2] (
@val double precision,
@pos int
)
RETURNS double precision
as begin
declare @tmpval1 int,
@tmpval2 double precision,
@retval double precision,
@tmpval3 decimal(38,20),
@tmpval4 decimal(38,20)
set @tmpval1 = floor(@val * power(cast(10 as float), @pos))
set @tmpval2 = round(@val, @pos, 1)
set @tmpval3 = (0.5 * power(cast(10 as float), (-1 * @pos)))
set @tmpval4 = @val - @tmpval2
set @retval = round(@val, @pos, case
when nullif(@tmpval1, (@tmpval1 / 2) * 2) is null
and @tmpval3 >= @tmpval4
then 1
else 0 end)
return @retval
end
David's, would appreciate it if one or both of you test these. I ran a small test, and it seems to work on 32-bit SQL 2000 and 64-bit SQL 2005.
Thanks!
-
Mmmhhh, if, as the link says, 64 bit uses bits 0-51 for the mantissa or significand then I wonder why SQL server lets you specify float(53). I thought double precision was pretty universal whether it's done via a true 64 bit processor or 'simulated' on a 32-bit processor. Somewhere, a bit got lost.
-
Why "double precision" (why you guys are playing with this name, it's synonym of "float"!) is not acceptable see the link posted by David Jackson.
For decimal(38, 20) you did not fix anything.
I posted values to test, error is the same:
Declare @a decimal(38,20)
Declare @b-2 decimal(38,20)
SELECT [dbo].[fn_BRound] (@A/@B, 2)
--------------------
.04000000000000000000
_____________
Code for TallyGenerator -
Lynn, initial tests on the decimal version look pretty good. It even passed Sergiy's test, as can be seen below. When his 5/111.111 is passed to the function, it receives 0.04500000000000000000, which should round down to .04. It does, as your function returns 0.04000000000000000000. I'll do some more testing, but initial impressions are good (I spot checked some other things and all came out good, but I want to do some large sample sets). The built-in Round() function will return .05 for this test, but that's what you would expect from an input of 0.04500000000000000000.
Declare @a decimal(38,20)
Declare
@b-2 decimal(38,20)
Declare
@C decimal(38,20)
SET
@a = 5
SET
@b-2 = 111.111
SET
SELECT
,[dbo].[fn_BRoundLynn] (@a/@b-2, 2)
-
The problem is
5/111.111 > 0.04500000000000000000
Welcome to the real world.
_____________
Code for TallyGenerator
Viewing 15 posts - 61 through 75 (of 378 total)
You must be logged in to reply to this topic. Login to reply