June 14, 2010 at 2:30 am
I noticed Microcoft Excel seems to not really round-down, when you say format cells to 2 decimal places, but its what I am depending on for real money calculations and I need SSK25 to behave the same way.
Consider the following:
declare @vatAmt decimal(18,2)
set @vatAmt = 0.175 --Is vat am applying
declare @fee decimal(18,2)
set @fee = 0.05 --Is a fee am applying
declare @realMoney TABLE
(
amount decimal(18,2),
txFee decimal(18,2),
vat decimal(18,2)
);
INSERT INTO @realMoney
( amount, txFee, vat)
SELECT 20.50,null, null UNION ALL
SELECT 100,null, null UNION ALL
SELECT 15,null, null UNION ALL
SELECT 30 ,null, null
update @realMoney
set txFee = cast ( round(amount * @fee,2) as decimal(18,2))
, vat = cast ( round( round(amount * @fee ,2) * @vatAmt ,2) as decimal(18,2))
select *
from @realMoney
The results in Excel are considerably lower than this which is the result in SS2K. Thanks for your input! :
amount txFee vat
--------------------------------------- --------------------------------
20.50 1.03 0.19
100.00 5.00 0.90
15.00 0.75 0.14
30.00 1.50 0.27
June 14, 2010 at 3:21 am
Here's some good information on rounding. Maybe you can use one of the functions in Excel or convert one to SQL.
June 14, 2010 at 4:37 am
In your case the result is nothing to do with the ROUND function.
Check what value you have in your @vatAmt variable after setting it as you did. Then change its declaration to :
declare @vatAmt decimal(18,3)
You will be surprised by results 😀
June 14, 2010 at 6:16 am
oh dear! Thanks very very much!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply