Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88

    Unanswered: 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?

  2. #2
    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
    ADP VBA: 1665.92 Rounds up

    Does this make sense?
    try using

    select ROUND(17 * 97995.0 / 1000.0, 2)

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

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I am getting

    select ROUND(17.0 * 97995.0 / 1000.0,2)

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


    Is this not what you wanted?

  5. #5
    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
    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)

  6. #6
    Join Date
    Jun 2003
    Location
    Canada
    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. #7
    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. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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. #9
    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. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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. #11
    Join Date
    Jun 2003
    Location
    Canada
    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. #12
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88
    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.
    Last edited by ingineu; 02-20-04 at 18:36.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •