Sql Query round dollar value to five cents

I am trying to work out how to handle monetary transactions for purchases where cash is used and the amount needs to be rounded to the nearest 5 cents as Australian currency has no smaller denominations.
For example the following items are purchased
Orange 1.29
Banana 1.22

transaction total 2.51 but when they pay cash they would obviously pay 2.50.
Should we round the transaction before its stored in the DB for cash transactions only? or should we round all the reports based on the cash transactions?
I think saving it rounded makes more sense . Is their a way to have the SQL statement that checks the payment type and if payment type "cash" it will round to the nearest 5 cent? If so could someone provide me the statement to do this?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

this will do it:
round ( 20.00 * <expression> , 0) / 20.00

>>"5 cents as Australian currency has no smaller denominations."
yep, cash transactions must be rounded to 5 cents as we abolished 2 and 1 cent coins

I hate coins anyway, especially those absurdly small ones :)

>>Is their a way to have the SQL statement that checks the payment type and if payment type "cash"

   when pay_type='cash' then round ( 20.00 * [money_field] , 0) / 20.00
   else [money_field]

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dsackerContract ERP Admin/ConsultantCommented:
It may be good to store the original value AND the rounded value. You just never know when you'll need the original. If you decide to store it in two fields (original and cashamt), this would round your value as follows:

ROUND(OriginalAmt / 5, 2) * 5

Code example (assuming table and field names):

INSERT  INTO myTransTable (TransType, OriginalAmt, CashAmt, AnyOtherFields)
SELECT  TransType,
        CashAmt = CASE
            WHEN TransType <> 'CASH' THEN OriginalAmt -- or NULL if you prefer
            ELSE ROUND(OriginalAmt / 5, 2) * 5
FROM    Wherever

Open in new window

I would also add there is almost certainly an accountancy standard for this - but I don't know what it is I'm afraid

perhaps your accountant would know.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

monitorwaAuthor Commented:
thanks for the reply to both of you, both are correct and helpful, I will award you both full points but would also appreciate your comments concerning this same issue as follows

Orange 1.29
Banana 1.22

transaction total 2.51 but when they pay cash they would obviously pay 2.50.

So if we rounded the full transaction value to 2.5 it fixes the immediate problem but if running some reconciliation checks we find that tow items priced at 1.29 & 1.22 were sold at 2.5 instead of 2.51 leaving a discrepancy of 0.01, compared to a transaction for the exact same items paid for by credit card which would charge to the cent?
So we could simply round all item values to a value that fits which means they may loose or gain per transaction for credit payments?
So are either of you aware of any standard relating to this sort of situation?


Afraid I don't know the relevant standards for this feel fairly sure they would exist
Do you have an accountant of financial controller to refer this to?

You could try this list from Australian Accounting Standards Board (AASB):

or: they might help you find the relevant standard

I also looked at Standards Australia
but that didn't seem to be relevant.
monitorwaAuthor Commented:
chhers guys, ill try and remember to post what the accountant advises


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.