Certainly. This uses Double Precision, which I've never heard of in T-SQL until recently, but it still has trouble with certain values.
Although it does pass the following, which Lynns doesn't until you change the decimal data type to Double Precision.
select 100000000.01 / 800000000.01,
dbo.fn_Bround(100000000.01 / 800000000.01,100), --mine
dbo.fn_Bround2(100000000.01 / 800000000.01,2), --Lynns
Round(100000000.01 / 800000000.01,2)
select cast(1 as float) / cast(8 as float),
dbo.fn_Bround(cast(1 as float) / cast(8 as float),100),
Round(cast(1 as float) / cast(8 as float),2)
I've not bothered with 2/3 as we both know what that will do.
ALTER FUNCTION FN_BROUND
(@p1 DOUBLE PRECISION,
@p2 INT)
RETURNS MONEY
/*
Round-to-even method (from http://en.wikipedia.org/wiki/Rounding)
This method is also known as unbiased rounding or as statistician's rounding or as bankers' rounding.
It is identical to the common method of rounding except when the digit(s) following the rounding digit
start with a five and have no non-zero digits after it. The new algorithm is:
* Decide which is the last digit to keep.
* Increase it by 1 if the next digit is 6 or more, or a 5 followed by one or more non-zero digits.
* Leave it the same if the next digit is 4 or less
* Otherwise, all that follows the last digit is a 5 and possibly trailing zeroes;
then change the last digit to the nearest even digit. That is, increase the rounded
digit if it is currently odd; leave it if it is already even.
With all rounding schemes there are two possible outcomes: increasing the rounding digit by one or
leaving it alone. With traditional rounding, if the number has a value less than the half-way mark
between the possible outcomes, it is rounded down; if the number has a value exactly half-way or
greater than half-way between the possible outcomes, it is rounded up. The round-to-even method is the
same except that numbers exactly half-way between the possible outcomes are sometimes rounded up—sometimes down.
Although it is customary to round the number 4.5 up to 5, in fact 4.5 is no nearer to 5 than it is to 4
(it is 0.5 away from either). When dealing with large sets of scientific or statistical data, where trends
are important, traditional rounding on average biases the data upwards slightly. Over a large set of
data, or when many subsequent rounding operations are performed as in digital signal processing, the
round-to-even rule tends to reduce the total rounding error, with (on average) an equal portion of numbers
rounding up as rounding down. This generally reduces the upwards skewing of the result.
Round-to-even is used rather than round-to-odd as the latter rule would prevent rounding to a result of zero.
Examples:
* 3.016 rounded to hundredths is 3.02 (because the next digit (6) is 6 or more)
* 3.013 rounded to hundredths is 3.01 (because the next digit (3) is 4 or less)
* 3.015 rounded to hundredths is 3.02 (because the next digit is 5, and the hundredths digit (1) is odd)
* 3.045 rounded to hundredths is 3.04 (because the next digit is 5, and the hundredths digit (4) is even)
* 3.04501 rounded to hundredths is 3.05 (because the next digit is 5, but it is followed by non-zero digits)
-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.fn_BRound (282.26545, 100) -- 100 to nearest penny, 20 to nearest 5 pence, 10 to nearest 10 pence,
-- 5 to nearest 20 pence, 2 to nearest 50 pence, 1 to nearest pound/dollar/euro
Code (poorly?) converted from VB example @ http://support.microsoft.com/kb/196652
*/
AS
BEGIN
DECLARE @Temp DOUBLE PRECISION,
@FixTemp DOUBLE PRECISION
SELECT @Temp = @p1 * @p2
--This is the closest I can get, but it is still wrong for certain values
SELECT @FixTemp = SIGN(@Temp + 0.5 * SIGN(@p1)) * FLOOR(ABS(@Temp + 0.5 * SIGN(@p1)))
-- Handle rounding of .5 in a special manner
IF @Temp - FLOOR(@Temp) = 0.5
BEGIN
IF @FixTemp / 2 <> FLOOR(@FixTemp / 2) -- Is Temp odd
-- Reduce Magnitude by 1 to make even
SELECT @FixTemp = @FixTemp - SIGN(@p1)
END
RETURN @FixTemp / @p2
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/* Lynns Function is here as a sense check */
-----------------------------------------------------------------------------------------------
ALTER FUNCTION dbo.fn_Bround2
(@TestValue DECIMAL(38,12),
@pos INT)
RETURNS MONEY
AS
BEGIN
RETURN ROUND(@TestValue,@pos,CASE
WHEN (NULLIF(ROUND(@TestValue * POWER(CAST(10 AS DECIMAL(38,12)),@pos),
0,1),ROUND((@TestValue * POWER(CAST(10 AS DECIMAL(38,12)),@pos)) / 2,
0,1) * 2) IS NOT NULL)
OR (NULLIF(ROUND((@TestValue * POWER(CAST(10 AS DECIMAL(38,12)),@pos)),
0,1),ROUND((@TestValue * POWER(CAST(10 AS DECIMAL(38,12)),@pos)) / 2,
0,1) * 2) IS NULL
AND (@TestValue * POWER(CAST(10 AS DECIMAL(38,12)),@pos)) - ROUND((@TestValue * POWER(CAST(10 AS DECIMAL(38,12)),@pos)),
0,1) > .5) THEN 0
ELSE 1
END)
END
GO
HTH
Dave J
http://glossopian.co.uk/"I don't know what I don't know."