Bankers Rounding Work in Progress

This utility script demonstrates and compares banker's rounding versus standard SQL rounding for payroll calculations. It includes a custom function definition for banker's rounding and provides side-by-side comparison of rounding methods for specific employees and date ranges.

Parameters

• Date range is hardcoded: '6/12/2022' to '6/18/2022'
• Employee filekeys are hardcoded: 7929, 7940

Data Components

The script consists of 3 main sections:

1. Function Definition - Custom banker's rounding function (commented out)
2. Banker's Rounding Results - Pay data using custom rounding function
3. Standard Rounding Results - Pay data using SQL ROUND function

Output Format

Both result sets return pay data with the following structure:

Technical Implementation

The script uses:

• Custom scalar function for banker's rounding algorithm
• Aggregation functions (SUM) for hours and dollars
• GROUP BY for employee and pay designation summarization
• Comparison queries to demonstrate rounding differences

Banker's Rounding Function Logic

The commented function implements banker's rounding (round half to even):

• When the digit to be rounded is exactly 5, rounds to the nearest even number
• Uses modulo operation to determine if the preceding digit is even or odd
• Applies standard rounding for all other cases

Notes

• Function definition is commented out and needs to be created separately
• Hardcoded date range and employee selection for testing
• Demonstrates rounding differences in payroll calculations
• Banker's rounding reduces bias in large datasets
• Standard SQL rounding always rounds 0.5 up
• Results may show minimal differences depending on data
• Useful for compliance with specific accounting standards
• Should be tested thoroughly before production implementation

T-SQL

/*Create Function dbo.BankersRound(@Val Decimal(32,16), @Digits Int)
Returns Decimal(32,16)
AS
Begin
    Return Case When Abs(@Val - Round(@Val, @Digits, 1)) * Power(10, @Digits+1) = 5 
                Then Round(@Val, @Digits, Case When Convert(int, Round(abs(@Val) * power(10,@Digits), 0, 1)) % 2 = 1 Then 0 Else 1 End)
                Else Round(@Val, @Digits)
                End
End
*/

Select FILEKEY, PAYDESNUM, RATE, SUM(hours) as HOURS, dbo.BankersRound(SUM(dollars),2) as DOLLARS from PAYDESHIST

where EVENTDATE between '6/12/2022' and '6/18/2022'
and FILEKEY in (7929,7940)
group by FILEKEY, PAYDESNUM, RATE
order by 1,2

Select FILEKEY, PAYDESNUM, RATE, SUM(hours) as HOURS, Round(isnull(sum(DOLLARS),0),2) as DOLLARS from PAYDESHIST

where EVENTDATE between '6/12/2022' and '6/18/2022'
and FILEKEY in (7929,7940)
group by FILEKEY, PAYDESNUM, RATE
order by 1,2

Content Inventory

• Doc File: content/docs/reports/pay_information/bankers_rounding_wip.mdx
• SQL Script: SQL/reports/pay_information/bankers_rounding_wip.sql