Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««3435363738

Round to Even (aka Banker''s Rounding) - The final function Expand / Collapse
Author
Message
Posted Friday, July 13, 2007 10:55 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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?
Post #381684
Posted Friday, July 13, 2007 11:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
1 > undefined = undefined
1 > infinity = false

Now you tell me if what they are saying at this link is true.
Post #381685
Posted Friday, July 13, 2007 11:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,808, Visits: 32,746

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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #381687
Posted Sunday, July 15, 2007 3:54 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
> 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.
Post #381774
Posted Sunday, July 15, 2007 10:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,808, Visits: 32,746

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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #381793
Posted Monday, July 16, 2007 5:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 6, 2012 10:55 AM
Points: 2, Visits: 8

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)
 
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. 
Post #381879
Posted Thursday, February 21, 2013 7:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 9:18 AM
Points: 84, Visits: 88
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.12356

2. We have clients based in New Zealand. Will they be arrested?
Post #1422576
Posted Thursday, February 21, 2013 8:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 9:18 AM
Points: 84, Visits: 88
Urrgh

I 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)

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 @retval
end

Post #1422610
« Prev Topic | Next Topic »

Add to briefcase «««3435363738

Permissions Expand / Collapse