1. Registered User
Join Date
Jun 2003
Location
Posts
88

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

Does this make sense?

2. Registered User
Join Date
Feb 2004
Location
Rhode Island
Posts
69

## Re: Rounding differences

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

Does this make sense?
try using

select ROUND(17 * 97995.0 / 1000.0, 2)

3. Registered User
Join Date
Jun 2003
Location
Posts
88
That didn't change anything. Any other ideas?

4. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
I am getting

select ROUND(17.0 * 97995.0 / 1000.0,2)

-------------------
1665.92000000

Is this not what you wanted?

5. Registered User
Join Date
Feb 2004
Location
Chester Springs, PA
Posts
16

## Re: Rounding differences

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

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)

6. Registered User
Join Date
Jun 2003
Location
Posts
88
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?

7. Registered User
Join Date
Feb 2004
Location
Chester Springs, PA
Posts
16
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.

8. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
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

9. Registered User
Join Date
Feb 2004
Location
Chester Springs, PA
Posts
16
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!!!

10. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
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

11. Registered User
Join Date
Jun 2003
Location
Posts
88
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?

12. Registered User
Join Date
Jun 2003
Location