Rounding differences

I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different:
eg.
ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding

SQL SProc: 1665.91 Rounds down
ADP VBA: 1665.92 Rounds up

Does this make sense?Originally posted by ingineu
I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different:
eg.
ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding

SQL SProc: 1665.91 Rounds down
ADP VBA: 1665.92 Rounds up

Does this make sense?
try using

select ROUND(17 * 97995.0 / 1000.0, 2)|||That didn't change anything. Any other ideas?|||I am getting

select ROUND(17.0 * 97995.0 / 1000.0,2)

------
1665.92000000

Is this not what you wanted?|||Originally posted by ingineu
I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different:
eg.
ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding

SQL SProc: 1665.91 Rounds down
ADP VBA: 1665.92 Rounds up

Does this make sense?

My guess is that you've dimensioned the variable that catches the calculation (17 * 97995 / 1000,2) as something like a numeric(n,2) instead a numeric(n,2)|||Okay ... I had not tried the 17.0. When I enter all values with 1 decimal place, it works. However, this is not satisfactory as I am using fields in the calculation as follows:

SELECT #Work1.*
ZAmt = CASE
WHEN TestCd = 'A' THEN Round(TestVal * ZFlow / 1000, 2)
ELSE Round(TestVal * ZFlow2 / 1000, 2) END
FROM #Work1

How would I define ZAmt?|||Originally posted by ingineu
Okay ... I had not tried the 17.0. When I enter all values with 1 decimal place, it works. However, this is not satisfactory as I am using fields in the calculation as follows:

SELECT #Work1.*
ZAmt = CASE
WHEN TestCd = 'A' THEN Round(TestVal * ZFlow / 1000, 2)
ELSE Round(TestVal * ZFlow2 / 1000, 2) END
FROM #Work1

How would I define ZAmt?

You're going to want ZFlow, TestVal and ZFlow2 to all have at least 3 digits to the right of the decimal place. I'd also have ZAmt have at least 3 digits to the right of the decimal place.|||Assuming all of your fields are defined as integer (if they are defined as numeric or decimal, you are already set).

ZAmt = CASE
WHEN TestCd = 'A' THEN Round(convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow) / 1000.0, 2)
ELSE Round(convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow2) / 1000.0, 2) END|||Originally posted by MCrowley
Assuming all of your fields are defined as integer (if they are defined as numeric or decimal, you are already set).

ZAmt = CASE
WHEN TestCd = 'A' THEN Round(convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow) / 1000.0, 2)
ELSE Round(convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow2) / 1000.0, 2) END

NO!!!!! 20,3!!!!!

otherwise, you might as not use the rounding function at all!!!|||After testing on a machine here:

create table test1
(testcd varchar(10),
testval int,
zflow int,
zflow2 int)
go
insert into test1
values ('a', 10, 10, 10)

insert into test1
values ('b', 10, 10, 1)

insert into test1
values ('a', 1, 5, 10)

select ZAmt = CASE
WHEN TestCd = 'A' THEN Round(convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow) / 1000.0, 2)
ELSE Round(convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow2) / 1000.0, 2) END ,
testcd, convert(numeric(20,2), TestVal) * convert(numeric(20,2), ZFlow) / 1000.0
from test1
go
drop table test1|||My fields are defined as 'float'. I tried the 'convert(numeric' with no change. Do I need to convert the float field? Is there no function around that takes a column and Rounds it to whatever significant digits you request?|||I tried it with the (20,3) and it works. Thanks for helping me out on this one. Much appreciated. I decided to do it in 2 steps, 1. calculate the column, 2. Round the column, for accuracy.