Bankers Rounding

Question

Post reply

Bankers Rounding

Viewing 15 posts - 226 through 240 (of 373 total)

You must be logged in to reply to this topic. Login to reply

David Jackson SSCertifiable Points: 6414 More actions · Answer 1

Sorry. I can't resist.

This works for me.

declare @d decimal(30,20),

@a decimal(30,20),

@b-2 decimal(30,20)

select @a = 2

select @b-2 = 3

select @d = @a / @b-2

select @a

select @b-2

select @d

-- 100 to closest penny, 20 to closest 5 pence, 10 to closest 10 pence,

-- 5 to closest 20 pence, 2 to closest 50 pence, 1 to closest pound/dollar/euro

select dbo.fn_Bround(@d,1) [closest dollar]

select dbo.fn_Bround(@d,2) [closest 50 cents]

select dbo.fn_Bround(@d,5) [closest 20 cents]

select dbo.fn_Bround(@d,10) [closest 10 cents]

select dbo.fn_Bround(@d,20) [closest 5 cents]

select dbo.fn_Bround(@d,100) [closest cent]

Results in:

--------------------------------

2.00000000000000000000

--------------------------------

3.00000000000000000000

--------------------------------

.66666666000000000000

closest dollar

---------------------

1.0000

closest 50 cents

---------------------

.5000

closest 20 cents

---------------------

.6000

closest 10 cents

---------------------

.7000

closest 5 cents

---------------------

.6500

closest cent

---------------------

.6700

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Are you sure .66666666000000000000 = 2/3?

I checked:

@d * @b-2 = 1.99999998000000000

It's not = 2.

So, can you please provide script where BR is rounding 2/3, but not any numbrer around?

_____________
Code for TallyGenerator

David Jackson SSCertifiable Points: 6414 More actions · Answer 3

As I said, it works for me!

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Or you don't insist on presision on those numbers anymore?

And now you do agree with me that 0.66666666 is valid perpresentations for all numbers between 0.66666666(0) and 0.66666666(9), including 2/3 which is 0.66666666(6)?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Does this work for you?

declare @d decimal(30,20),

@a decimal(30,20),

@b-2 decimal(30,20)

select @a = 1.00000000008

select @b-2 = 8.00000000000

select @d = @a / @b-2

select @a

select @b-2

select @d

SELECT @d*@b

select dbo.fn_Bround(@d,1) [closest dollar]

select dbo.fn_Bround(@d,2) [closest 50 cents]

select dbo.fn_Bround(@d,5) [closest 20 cents]

select dbo.fn_Bround(@d,10) [closest 10 cents]

select dbo.fn_Bround(@d,20) [closest 5 cents]

select dbo.fn_Bround(@d,100) [closest cent]

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Still not sure?

Try this:

declare @d decimal(30,20),

@a decimal(30,20),

@b-2 decimal(30,20)

select @a = 100001

select @b-2 = 800000

select @d = @a / @b-2

select @a

select @b-2

select @d

SELECT @d*@b

select dbo.fn_Bround(@d,1) [closest dollar]

select dbo.fn_Bround(@d,2) [closest 50 cents]

select dbo.fn_Bround(@d,5) [closest 20 cents]

select dbo.fn_Bround(@d,10) [closest 10 cents]

select dbo.fn_Bround(@d,20) [closest 5 cents]

select dbo.fn_Bround(@d,100) [closest cent]

_____________
Code for TallyGenerator

David Jackson SSCertifiable Points: 6414 More actions · Answer 7

This works perfectly as expected for me

set

nocount on

declare @d decimal(30,20),

@a

decimal(30,20),

@b-2

decimal(30,20)

select

@a = 100001

select

@b-2 = 800000

select

@d = @a / @b-2

select

@a

select

@b-2

select

@d

SELECT

@d*@b-2

select

dbo.fn_Bround(@d,1) [closest dollar]

select

dbo.fn_Bround(@d,2) [closest 50 cents]

select

dbo.fn_Bround(@d,5) [closest 20 cents]

select

dbo.fn_Bround(@d,10) [closest 10 cents]

select

dbo.fn_Bround(@d,20) [closest 5 cents]

select

dbo.fn_Bround(@d,100) [closest cent]

--Lynns method

select

dbo.fn_Bround2(@d,0) [No Digits]

select

dbo.fn_Bround2(@d,1) [1 Digit]

select

dbo.fn_Bround2(@d,2) [2 Digits]

select

dbo.fn_Bround2(@d,3) [3 Digits]

select

dbo.fn_Bround2(@d,4) [4 Digits]

Results in:

---------------------------------------

100001.00000000000000000000

---------------------------------------

800000.00000000000000000000

---------------------------------------

0.12500125000000000000

---------------------------------------

100001.00000000000000000

closest dollar

---------------------

0.00

closest 50 cents

---------------------

0.00

closest 20 cents

---------------------

0.20

closest 10 cents

---------------------

0.10

closest 5 cents

---------------------

0.15

closest cent

---------------------

0.13

No Digits

---------------------

0.00

1 Digit

---------------------

0.10

2 Digits

---------------------

0.13

3 Digits

---------------------

0.125

4 Digits

---------------------

0.125

Now try @a = 100000

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Here's myresults:

closest dollar

---------------------

.0000

closest 50 cents

---------------------

.0000

closest 20 cents

---------------------

.2000

closest 10 cents

---------------------

.1000

closest 5 cents

---------------------

.1000

closest cent

---------------------

.1200

I used function posted here.

Which one did you use?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Still waiting for a script rounding 2/3.

Is it too hard for you?

Probably you could ask SQL professional for a help?

_____________
Code for TallyGenerator

David Jackson SSCertifiable Points: 6414 More actions · Answer 10

closest cent

---------------------

.1200

is the result you get when you use 100000

And it is expected. This is why bankers rounding is also called the round to even method. Re-read the code I posted, it uses both functions posted to this thread, mine (or Microsofts) and Lynnes.

Re the 2/3 question, I did post code, and I said it was good enough for my applications purpose.

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Cory E. SSCertifiable Points: 7076 More actions · Answer 11

I have to admit, I have been watching this topic nearly since it started. I have to give kudo's to Sergiy, he has stuck to his guns. He does have a point worth considering. That said, I think that the others have done a great job proving any questions posed, and from what I can tell, this is a draw. . .

Just my (precise) 2 cents. I do not really want to be dragged in the middle, but did want to show the possibility of more than 5 users still watching this.

C

-- Cory

JacekO SSCertifiable Points: 6610 More actions · Answer 12

Oh boy, I think there is more then 5 of us watching this...

I just don't understand what the argument is all about anymore....

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Anders Pedersen SSChampion Points: 11410 More actions · Answer 13

Most entertaining thread on this site in years!

(Now someone know Giordy's real name so I can make sure I never take a job where I will have to interact with him?)

He reminds me of this one database professional (HA!) that happened to be a proffessor. He designed a database (in SQL 6.5) to 5th (might have been 4.99999) normalized form. And couldn't figure out why it wouldn't work, surely there is no reason why we can't have 30+ join statements in one query?

Jeff Gray SSChampion Points: 10667 More actions · Answer 14

It's because there is no such thing as the number 30. 30 represents any number of joins from 29.5 to 30.5, except at 3 picoseconds after midnight, when the correct answer is "orange".

*pops more popcorn*

Anders Pedersen SSChampion Points: 11410 More actions · Answer 15

Hey Jeff, for you:

http://worsethanfailure.com/Articles/OMGWTF-Finalist-08-Universal-Calculator.aspx