SQL SERVER – Banker’s Rounding

Rounding numbers is common in many applications, including financial calculations, statistical analysis, and data presentation. While several rounding methods are available, banker’s rounding stands out for its precision and fairness. Also known as unbiased rounding or round half to even, banker’s rounding follows a set of rules that ensures numbers are rounded to the nearest even number when the digit to be rounded is exactly 5. In this blog post, we will explore the concept of banker’s rounding and demonstrate how to implement it in SQL Server.

Banker’s Rounding Rules

Banker’s rounding adheres to the following rules:

• If the number after the digit to be rounded is 5 or more, round up.
• If the number after the digit to be rounded is less than 5, round down.
• If the number after the digit to be rounded is exactly 5, round to the nearest even number.

For example, if we want to round the number 2.5 to the nearest integer:

• In traditional rounding (round half up), 2.5 would be rounded to 3.
• In banker’s rounding, 2.5 would be rounded to 2 (the nearest even number).

Implementing Banker’s Rounding in SQL Server

To implement banker’s rounding in SQL Server, we can create a user-defined function (UDF) that takes the number to be rounded and the desired number of decimal places as input parameters. Here’s an example implementation:

-- Create a function for banker's rounding
CREATE FUNCTION BankersRound(@num decimal(10,2), @decimalPlaces int)
RETURNS decimal(10,2)
AS
BEGIN
DECLARE @multiplier decimal(10,2) = 10^(@decimalPlaces)

DECLARE @roundedNum decimal(10,2)

-- Round to nearest even if next digit is 5
IF (ROUND(@num * @multiplier, 0, 1) % 2 = 1 AND
RIGHT(CAST(@num * @multiplier AS bigint), 1) = 5)
SET @roundedNum = ROUND(@num, @decimalPlaces, 1)
-- Normal rounding
ELSE
SET @roundedNum = ROUND(@num, @decimalPlaces)

RETURN @roundedNum
END

Let’s break down the implementation:

1. We define the function ‘BankersRound’ with two parameters: ‘@num’ represents the number to be rounded, and ‘@decimalPlaces’ indicates the desired number of decimal places in the rounded result.
2. Inside the function, we declare a variable ‘@multiplier’ to calculate the scaling factor. It is set to 10 raised to the power of ‘@decimalPlaces’.
3. We declare another variable, ‘@roundedNum’ to store the rounded result.
4. The implementation checks if the next digit after the digit to be rounded is 5. If it is, and the rounded number multiplied by the scaling factor is odd (checked using modulo 2), we know we need to round to the nearest even number. In this case, we round the number using the ‘ROUND’ function with the third parameter set to 1, indicating rounding to the nearest even number.
5. If the next digit is not 5, or if the rounded number multiplied by the scaling factor is even, we apply normal rounding using the ‘ROUND’ function without the third parameter.
6. Finally, we return the rounded number.

Using the Rounding Function

Once the ‘BankersRound’ function is created, we can use it in SQL queries to perform banker’s rounding. Here are a few examples:

SELECT dbo.BankersRound(123.455, 2) -- Returns 123.46
SELECT dbo.BankersRound(123.445, 2) -- Returns 123.44

In the first example, the number 123.455 is rounded to 2 decimal places, resulting in 123.46. According to the banker’s rounding rules, since the number after the digit to be rounded (5) is greater than 5, we round up to the nearest even number, 6.

In the second example, 123.445 is rounded to 2 decimal places, resulting in 123.44. In this case, the number after the digit to be rounded (4) is less than 5, so we round down to the nearest even number, 4.

Conclusion

Banker’s rounding provides a fair and consistent method for rounding numbers. By rounding to the nearest even number when the digit to be rounded is exactly 5, it avoids any inherent bias towards rounding up or down. In SQL Server, we can implement banker’s rounding using a user-defined function like the one demonstrated in this blog post. The function takes the number to be rounded, and the desired number of decimal places as input parameters, and it applies the banker’s rounding logic accordingly.

By implementing banker’s rounding in SQL Server, you can ensure accurate and unbiased rounding in your calculations and data manipulations. It is particularly useful in financial applications where precision and fairness are crucial. I have taken the help of my friend Mark Jacobson to complete this blog post.

You can connect with me on X over here.

Reference: Pinal Dave (https://blog.sqlauthority.com)