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?
PortletPaulConnect With a Mentor Commented:
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]
dsackerConnect With a Mentor Contract 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.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.