I have been trying to do ‘bankers rounding’ on the fly in a select query.
Here is what I have come up with. It seems to work but I would like another set of eyes to look at it in case I am missing something. This is a work in
progress so any feedback would be greatly appreciated.
SELECT total_tax AS x, RIGHT((TRUNCATE((total_tax * 1000), 0)), 1) AS y, RIGHT((CEIL(total_tax * 100)), 1) AS z,
CASE WHEN ( ((RIGHT((TRUNCATE((total_tax * 1000), 0)), 1))='5' AND (RIGHT((CEIL(total_tax * 100)), 1)) IN ('0','2','4','6','8')) OR ((RIGHT((TRUNCATE((total_tax * 1000), 0)), 1))>'5') ) THEN (
((CEIL(total_tax * 100)) / 100)
) WHEN ( ((RIGHT((TRUNCATE((total_tax * 1000), 0)), 1))='5' AND (RIGHT((CEIL(total_tax * 100)), 1)) IN ('1','3','5','7','9')) OR ((RIGHT((TRUNCATE((total_tax * 1000), 0)), 1))<'5') ) THEN (
((FLOOR(total_tax * 100)) / 100)
) END AS cart_total_tax_bnk FROM cart_items