Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    Colombo, Sri Lanka
    Posts
    6

    Question Unanswered: SQL Server Rounding Problem

    Background:
    I work in the clothing industry and historically clothes have always been counted in dozens in the format 9.99 where 1.04 = 16, 1.11 = 23, 2.00 = 24. We have always stored our data in our database in singles and convert to and from dozens for screen displays. We receive our goods in boxes from the factories. For example is we might receive a box of boxer shorts. The boxer shorts are pre-packaged in packs of 3. The box has 6 packs of box shorts making a total of 18 pairs of boxer shorts in a box. With me so far?

    So I have a box of 1.06 dozens, a pack size of 3 and I want to know how many packs are in the box. For the past couple of years we have been using this code:

    declare @PacksPerBox tinyint
    declare @boxsize real
    declare @packsize smallint
    set @boxsize = 1.06
    set @packsize = 3
    select @PacksPerBox = convert(tinyint,(((Round(@Boxsize,0,1)*12)+(CAST(( @Boxsize * 100) as int)%100))/@Packsize))
    print @PacksPerBox

    This code runs in a SQL Server 2000 UDF but the error also occurs in a SQL Server v7.0 SP. The result of this calculation should be 6 but is in fact 5. The issue is with the code CAST((@Boxsize * 100) as int) which returns 105 instead of 106.

    My question is:
    Is this an actual error in SQL Server? Is it poor practice by ourselves on the way we have put together the SQL statement?

    If you try many other box sizes e.g. 4.06 the formula works fine. I have solved the problem by changing the boxsize declaration from real to float but I'm wondering whether I am just moving the problem about rather than solving it?

  2. #2
    Join Date
    Jun 2003
    Location
    Colombo, Sri Lanka
    Posts
    6

    Re: SQL Server Rounding Problem

    For the record it appears that both real and float data types are prone to anomolies like this and should only be used for extremely large numbers where 100% precision is not required. I need to use something like decimal(5,2) instead.

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Well these Guys were'nt much help Shandy.

    Methinks when I've hit this prob before I Just Kept trying the Diff Data Types in a controlled statement.

    I find I use Decimal quite often now rather than float or real cos I had simialar probs

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    PS.

    My question is:
    Is this an actual error in SQL Server? Is it poor practice by ourselves on the way we have put together the SQL statement?
    I Certainly don't think it was

    poor practice by ourselves on the way we have put together the SQL statement?
    If not being already aware of all of the quirks of SQL Server like this is Poor Practice then - I think we're all in trouble

    Calculation limitations of SQL Server are'nt neccesarily errors - if they're consistent

    Did someone say that it was
    poor practice by ourselves on the way we have put together the SQL statement?
    lol

    tehe
    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    The problem is NOT an SQL problem, but an computer math problem as in the problem would exist on most computers using floating point numbers..

    Here's code that works for me. The problem is that an CEILING function needs added when converting from float/real to Integer ( Note round funtion should also have been OK, but you would have to add an value of .5 before doing the rounding.

    PS. This code should be tested for all values, must test for 0.00 and 0.01 they are the most likely to not work. ( I forgot to test them.)

    Note: This code SHOULD NOT be used on Negative values.

    Tim S

    declare @PacksPerBox tinyint
    declare @boxsize real
    declare @packsize smallint
    -- set @boxsize = 0.06 -- 6
    -- set @boxsize = 1.04 -- 16
    set @boxsize = 1.06 -- 18/packsize
    -- set @boxsize = 1.11 -- 23
    -- set @boxsize = 2.00 -- 24
    -- set @boxsize = 3.06 -- 42
    set @packsize = 3
    select @PacksPerBox = convert(tinyint,(((Round(@Boxsize,0,1)*12)+(CAST(C EILING(@Boxsize * 100)as int)%100))/@Packsize))
    print @PacksPerBox
    Last edited by TimS; 06-15-03 at 17:28.

Posting Permissions

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