April 13, 2012 at 9:30 am
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
May 4, 2012 at 8:35 am
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
August 10, 2023 at 8:07 pm
This was removed by the editor as SPAM
August 10, 2023 at 8:24 pm
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:
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
END
Some 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 BankRound
And the results:
September 15, 2023 at 10:03 pm
@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
end
I also am amazed that it seems to have taken decades of attempts!
September 16, 2023 at 2:46 am
@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
Change is inevitable... Change for the better is not.
September 16, 2023 at 2:48 am
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
end
This 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
September 16, 2023 at 2:56 am
It's still a scalar function and your retry explains why this thread took so long and died.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2023 at 3:13 am
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.DebitBal
In 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?
September 16, 2023 at 9:36 pm
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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 46 through 54 (of 54 total)
You must be logged in to reply to this topic. Login to reply