Rounding - how to do "Bankers Rounding" in SQL Server 2000
SLP
Have been searching for days for a solution to this problem, and have come
across pages and pages of interesting dialogue about the pro's/cons of
various rounding techniques, but no solution!
Background is: database was formerly an Access db, used for 5 years. One
of its purposes is to calculate pays, using an hourly-rate field (double, 5
decimals) * number of hours field (double, 2 decimals). Access has always
used "Bankers Rounding" to derive the amount.
The database has now been migrated to SQL Server 2000, but I'm having a
problem achieving the same "result" for historical records. SQL Server
appears to be using "standard" rounding, and so there are hundreds of
records where the result is .01 out!
Can anyone give me some tips on how I can perform bankers rounding in SQL
Server? In Access, I used a function CCur(x / 100) * 100.
Thanks,
Sharon.
Scott Morris
anything built in.
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q196652
SLP
the MS KB, but hadn't found this one.
The BRound() function looks like it might be what I need. I'm new to SQL
Server, but I figure the User Defined Functions work in a similar way to
those in Access. Will play around with this one.
Sharon.
"Scott Morris" <te...@x.com> wrote in message news:3C2B8C63...@x.com...
James Morton
"Banker's rounding rounds .5 up sometimes and down sometimes. The convention
is to round to the nearest even number, so that both 1.5 and 2.5 round to 2,
and 3.5 and 4.5 both round to 4. Banker's rounding is symmetric."
A user defined function could take the decimal number and convert it to a
string, find the last character, determine if its a 5, and determine if the
number before the decimal was an odd or even integer, then round up or down
appropriatly (odds rounded up, evens rounded down). If the last character
was not a 5 then it would use the normal round() function. If you need help
writing this I am always glad to help out. There might already be a
function/proc out there, its defeinetly not in BoL or a proc in master.
James Morton
MSSQL DBA
PNM
"SLP" <slpn...@yahoo.com> wrote in message
news:uIzIiixjBHA.2284@tkmsftngp05...
Bob
> Thanks, Scott. What a handy article! I'd done a search for "Rounding" in
> the MS KB, but hadn't found this one.
>
> The BRound() function looks like it might be what I need. I'm new to SQL
> Server, but I figure the User Defined Functions work in a similar way to
> those in Access. Will play around with this one.
>
> Sharon.
>
queries. SQL Server does not. SQL Server 2000 introduced the idea of User
Defined Functions, but they are not the same as in Access. In SQL 2K, User
Defined Functions are written using T-SQL.
Bob - who has encountered this problem more than once
Gert-Jan Strik
It's a bit tricky, but this should do the trick:
round( NumToRound - (cast( cast(NumToRound+1 as int) % 2 as
decimal(10,1))/10) , 0 )
What it basically does, is use the Modulo function (%) to determine if
it's an even number or not. Because by default .5 is rounded up, I
needed a 1 for even number, that's why the modulo is done over
NumToRound + 1. This 1 is then translated to .1, and this is enough
correction to have 2.5 rounded down.
Hope this helps.
Gert-Jan
SLP
getting! (Coming down with a heavy head-cold a few days ago certainly hasn’
t helped things, either!!!)
My problem lies in trying to achieve the same result in SQL Server 2000 that
the Access database has reported for historical records.
In Access, the function CCur(X/100)*100 has been used for all calculations.
I thought I could probably do the same calculation in SQL Server, using it’s
equivalent functions … NOT. The closest I could come (and maybe this is
where I’m way off) was:
Convert(money, X/100)*100
… but this gives me exactly the same result as Round(X, 2) … which is giving
me the .01 variation in some calculations … which is what brought me here in
the first place. For example (hopefully word-wrap won’t screw up the
columns):
Rate Hrs Rt*Hrs Access Round
Convert
17.375 0.6 10.425 10.42 10.43
10.43
13.1658 1.9 25.01502 25.01 25.02
25.02
11.0158 0.95 10.46501 10.46 10.47
10.47
13.1658 3.75 49.37175 49.37 49.37
49.37
22.37 18.5 413.845 413.84 413.85
413.85
13.12735 9.03 118.5399705 118.54 118.54
118.54
10.6947 12.2 130.47534 130.48 130.48
130.48
12.3842 1.16 14.365672 14.37 14.37
14.37
So … I figured I would try:
1. Round to 3 decimals
2. Use Iif() with Right() to test both the last and second last digits.
3. If we’ve got ‘evens’5, then truncate at 2 decimals, else round to 2
decimals
But this introduces me to a new problem … seems I can’t use Iif() in a
View!!! And trying to create a user-defined function is, as Bob pointed
out, NOT like creating functions in VBA.
Aarrrggghhhhhhh. Help, please???
Thanks,
Sharon.
"James Morton" <jmo...@pnm.com> wrote in message
news:OZ8WBDyjBHA.3588@tkmsftngp04...
BP Margolin
The first thing I'd question is the accuracy of the rounding being done in
Access.
For example, 25.01502 , regardless of Bankers Rules or not, is properly
rounded, to two decimal places as 25.02 not 25.01.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"SLP" <slpn...@yahoo.com> wrote in message
news:#biu#2KkBHA.1860@tkmsftngp05...
Gert-Jan Strik
the first thing I noticed when I saw your data was, that Access does not
do Bankers Rounding. Is seems MS Access looks at the next digit in case
of a .5. For example, 10.46501 is rounded down to 10.46, but 14.36572 is
rounded up to 14.37. With Bankers Rounding, this should have been 10.46
and 14.36.
The example below gives you the Bankers Rounding. For the explanation,
see my previous post with similar solution.
CREATE TABLE #t
(Rate decimal(10,5)
,Hrs decimal( 5,2)
,Amount money
,AmountRounded money
)
INSERT INTO #t (Rate,Hrs) VALUES (17.375 , .6 )
INSERT INTO #t (Rate,Hrs) VALUES (13.1658 , 1.9 )
INSERT INTO #t (Rate,Hrs) VALUES (11.0158 , 0.95)
INSERT INTO #t (Rate,Hrs) VALUES (13.1658 , 3.75)
INSERT INTO #t (Rate,Hrs) VALUES (22.37 ,18.5 )
INSERT INTO #t (Rate,Hrs) VALUES (13.12735, 3.75)
INSERT INTO #t (Rate,Hrs) VALUES (10.6947 ,12.2 )
INSERT INTO #t (Rate,Hrs) VALUES (12.3842 , 1.16)
UPDATE #t
SET Amount = Rate * Hrs
UPDATE #t
SET AmountRounded =
ROUND( CAST(Amount - .0005 AS decimal(14,3)) -
(CAST( CAST(Amount*100 + .99 AS int) % 2 AS decimal(14,3))/1000)
, 2 )
SELECT * FROM #t
Rate Hrs Amount AmountRounded
------------ ------- --------------------- ---------------------
17.37500 .60 10.4250 10.4200
13.16580 1.90 25.0150 25.0200
11.01580 .95 10.4650 10.4600
13.16580 3.75 49.3718 49.3700
22.37000 18.50 413.8450 413.8400
13.12735 3.75 49.2276 49.2300
10.69470 12.20 130.4753 130.4800
12.38420 1.16 14.3657 14.3600
(8 row(s) affected)
Gert-Jan
SLP
news:#Qw6QIMkBHA.2212@tkmsftngp05...
> Sharon,
>
> The first thing I'd question is the accuracy of the rounding being done in
> Access.
>
> For example, 25.01502 , regardless of Bankers Rules or not, is properly
> rounded, to two decimal places as 25.02 not 25.01.
"Gert-Jan Strik" <so...@toomuchspamalready.nl> wrote in message
news:3C2E5DBD...@toomuchspamalready.nl...
> Sharon,
>
> the first thing I noticed when I saw your data was, that Access does not
> do Bankers Rounding. Is seems MS Access looks at the next digit in case
> of a .5. For example, 10.46501 is rounded down to 10.46, but 14.36572 is
> rounded up to 14.37. With Bankers Rounding, this should have been 10.46
> and 14.36.
Thanks to both for your help, particularly the detailed example Gert-Jan.
Unfortunately, my problem isn't whether or not Access was rounding
correctly - rather 'how' to go about performing the same calculation in SQL
Server. I can see now that the rounding performed by the Access function
doesn't seem to comply with 'accepted' rounding techniques, making it all
the more difficult!
I've ended up solving my problem by simplying including a CASE statement in
the stored procedure used in my report, which makes an adjustment of +\- .01
where necessary.
The problem doesn't occur for new records - only historic 'closed-out'
records - and my users get a bit picky when they run (print) a report from
the new SQL Server database for a monthly period during last financial year
and in some places it's .01 different to the one they printed previously
from the Access database!
Sharon.