Banker''s rounding in T-SQL (like Math.Round in .NET)
-
Just realized the inner case statement was over complicated and I had a WHEN that could never be satisfied..
revised:
CREATE FUNCTION dbo.itvf_BankersRound(@Num decimal(38,9))
RETURNS TABLE
AS
RETURN
(
SELECT
CASE
WHEN
ROUND(@Num, 2) - @Num = .005
AND ROUND(@Num, 2) % .02 <> 0
THEN
ROUND(@Num, 2) - 0.01
ELSE ROUND(@Num, 2) END AS [Result]
)
GO
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler -
The default rounding method in .NET is Round-to-Even (or 'Bankers Rounding' as described in this thread). This type of rounding can be implemented in SQL Server quickly, easily and accurately using a CLR function that returns a value by simply using the .NET Decimal.Round method.
I've tested such a CLR function against around 15 million values of datatype 'money' and the query completed in around 25s. I tried the first solution in this thread and I decided to stop the query after 10 minutes. In my situation the CLR solution wins the race.
Chris
-
This was removed by the editor as SPAM
-
I'm amazed I still can't find an official or widely endorsed solution to this, but CodeMuddler's solution is the best I have found so far. CLR is not an option for us when using Azure SQL.
Here is our version with some simplifications and assumptions on our max monetary amounts:
- Round to 2 decimal places
- Truncate input to decimal(10,4) (max value is ~1M or 999999.9999)
- Return as decimal(8,2) (max value is ~1M or 999999.99)
Truncating to decimal(10,3) would probably also work, we just need the input to preserve enough decimal places for the rounding to work.
CREATE FUNCTION dbo.fnBankersRound(@Num decimal(10,4))
RETURNS decimal(8,2)
AS
BEGIN
RETURN CASE
WHEN ROUND(@Num, 2) - @Num = .005 AND ROUND(@Num, 2) % .02 <> 0
THEN ROUND(@Num, 2) - 0.01
ELSE ROUND(@Num, 2)
END
ENDSome tests to validate the rounding:
declare @val decimal(10,4);
set @val = 1.1149;
select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
set @val = 1.1150;
select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
set @val = 1.1249;
select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
set @val = 1.1250;
select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
set @val = 1.1349;
select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
set @val = 1.1350;
select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
select
100000000.01 / 800000000.01 as Value,
round(100000000.01 / 800000000.01, 2) as Round,
dbo.fnBankersRound(100000000.01 / 800000000.01) as BankRoundAnd the results:
-
@GrassHopper's solution does not work for negative numbers, so here's his version that does work for negative numbers:
create function dbo.RoundToEven(@num numeric(38,19)) returns decimal(19,2)
begin
return case
when abs(round(@num, 2) - @num) = 0.005 and round(@num, 2) % 0.02 != 0
then round(@num, 2) - 0.01 * sign(@num)
else round(@num, 2)
end
endI also am amazed that it seems to have taken decades of attempts!
- MarkCranness wrote:
@Grasshopper's solution does not work for negative numbers, so here's his version that does work for negative numbers:
create function dbo.RoundToEven(@num numeric(38,19)) returns decimal(19,2)
begin
return case
when abs(round(@num, 2) - @num) = 0.005 and round(@num, 2) % 0.02 != 0
then round(@num, 2) - 0.01 * sign(@num)
else round(@num, 2)
end
endI also am amazed that it seems to have taken decades of attempts!
It's ok. We're amazed that it took you decades to get here and then wrote a scalar function instead of an iTVF. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I like this version better, because it does not have the magic constant "0.005" in it:
create function dbo.RoundToEven(@num numeric(38,19)) returns decimal(19,2)
begin
return case
when round(@num, 2, 1) % 0.02 = 0 then
round(@num - round(@num, 0) - sign(@num), 2) + round(@num, 0) + sign(@num)
else round(@num, 2)
end
endThis works because round(d, n) is "round away from zero", and if we need to round (say) 1.125 towards even, that is round down/TOWARDS zero, then calculate round(1.125 - 2, 2) + 2 = round(-0.875, 2) + 2 = -0.88 + 2 = 1.12.
Other dialects of SQL might instead use TRUNCATE(n, d) in place of ROUND(n, d, f):
-- Non-MS SQL Server:
create function dbo.RoundToEven(@num numeric(38,19)) returns decimal(19,2)
begin
return case
when truncate(@num, 2) % 0.02 = 0 then
round(@num - round(@num, 0) - sign(@num), 2) + round(@num, 0) + sign(@num)
else round(@num, 2)
end
end -
It's still a scalar function and your retry explains why this thread took so long and died.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Today I found out what a SQL Inline Table Value Function is!
I can't understand how an iTVF would be easier to use, I have a calculation like s0:
select t.Key
, dbo.RoundToEven(t.CreditBal - t.DebitBal)
- isnull(
sum(dbo.RoundToEven(e.Amount - e.Discount)
+ dbo.RoundToEven(dbo.RoundToEven(e.Amount - e.Discount) * TaxRate))
, 0)
from [Table] t
left join TransactionEntries e on t.Key = e.Key
group by t.Key, t.CreditBal, t.DebitBalIn the database, the values are Float (not my design!), but I need numeric(19.2) results of that calculation.
How would an iTVF work in this case?
- MarkCranness wrote:
Today I found out what a SQL Inline Table Value Function is!
I can't understand how an iTVF would be easier to use, I have a calculation like s0:
select t.Key
, dbo.RoundToEven(t.CreditBal - t.DebitBal)
- isnull(
sum(dbo.RoundToEven(e.Amount - e.Discount)
+ dbo.RoundToEven(dbo.RoundToEven(e.Amount - e.Discount) * TaxRate))
, 0)
from [Table] t
left join TransactionEntries e on t.Key = e.Key
group by t.Key, t.CreditBal, t.DebitBalIn the database, the values are Float (not my design!), but I need numeric(19.2) results of that calculation.
How would an iTVF work in this case?
An iTVF is not necessarily "easier to use", nor is that its purpose. It's purpose is to be nasty fast.
Rather than taint this already tainted thread, I recommend you start a new thread for you new question. In the meantime, have a gander at the following article. It may explain how to incorporate your code into an iTVF but it will certainly explain why to use them instead of Scalar functions. It's a very old but still appropriate article even today.
https://www.sqlservercentral.com/articles/how-to-make-scalar-udfs-run-faster-sql-spackle
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 46 through 54 (of 54 total)
You must be logged in to reply to this topic. Login to reply