Force a round() when it's below a 5?

I have a sales tax function in my antiquated system. Someone decided to
play it safe and over collect on sales tax.
If your bill = 9.53 @. 9.25% your total would be 10.4115. My Tax in the
system shows 10.42
How do I force the round up for reporting to Auditors when they want to
query random samples of data?
TIA
__StephenTry:
select ceiling (10.4115 * 100) / 100
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:eKkPqOn%23FHA.4004@.TK2MSFTNGP14.phx.gbl...
>I have a sales tax function in my antiquated system. Someone decided to
>play it safe and over collect on sales tax.
> If your bill = 9.53 @. 9.25% your total would be 10.4115. My Tax in the
> system shows 10.42
> How do I force the round up for reporting to Auditors when they want to
> query random samples of data?
> TIA
> __Stephen
>|||Lookup CEILING in Books Online.
ML
http://milambda.blogspot.com/|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uvnkFSn%23FHA.532@.TK2MSFTNGP15.phx.gbl...
> Try:
> select ceiling (10.4115 * 100) / 100
Thanks. With your credentials I'll give it a whirl. :)
Will this work within a SUM() when I'm grouping by State, Client, City.
Some clients have different contracts with us and we compute tax on where
the HQ of company is that signed the contract, and not where the recipient
is located.
I'm processing 250,000 + detail rows a month creating a final result of 110
rows summarized today.|||Sure. I assume that this round-up has to happen on each line item before it
is summed. You can make it conditional, too. For example:
select
sum (case when State in ('CA', 'TX') then ceiling (Tax * 100) / 100 else
Tax end)
from
MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:OaaHuYn%23FHA.208@.tk2msftngp13.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uvnkFSn%23FHA.532@.TK2MSFTNGP15.phx.gbl...
> Thanks. With your credentials I'll give it a whirl. :)
> Will this work within a SUM() when I'm grouping by State, Client, City.
> Some clients have different contracts with us and we compute tax on where
> the HQ of company is that signed the contract, and not where the recipient
> is located.
> I'm processing 250,000 + detail rows a month creating a final result of
> 110 rows summarized today.
>
>|||Back in the dark ages, we rounded by adding and truncating. For
example, to always round up with 2 decimal places,
SELECT cast((9.53 * 1.0925 + .009999) * 100 as integer) / 100.
Not sure its any simpler.
Good luck.
Payson
Tom Moreau wrote:
> Try:
> select ceiling (10.4115 * 100) / 100
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:eKkPqOn%23FHA.4004@.TK2MSFTNGP14.phx.gbl...