Professional Web Applications Themes

Special Rounding - Microsoft SQL / MS SQL Server

Hi, I need to do rounding on numbers but while doing so I shouldn't get more/less of the total value. I am running on SQL Server 2000. Can someone provide me some help/hints on how to achieve this. Thanks Suresh Please find the script attached below. USE PUBS GO DROP TABLE SOP_Rounding GO CREATE TABLE SOP_Rounding( CustomerNbr VARCHAR(10) NOT NULL, DTVNbr INT NOT NULL, CalendarWeekNbr INT NOT NULL, SellinQty DECIMAL(5,2) NULL ) GO INSERT INTO SOP_Rounding VALUES ( '5062766' , 4565 , 200332, .00) INSERT INTO SOP_Rounding VALUES ( '5062766' , 4565 , 200337, .67) INSERT INTO SOP_Rounding VALUES ( ...

  1. #1

    Default Special Rounding

    Hi,
    I need to do rounding on numbers but while doing so I
    shouldn't get more/less of the total value. I am running
    on SQL Server 2000. Can someone provide me some
    help/hints on how to achieve this.
    Thanks
    Suresh

    Please find the script attached below.

    USE PUBS
    GO
    DROP TABLE SOP_Rounding
    GO
    CREATE TABLE SOP_Rounding( CustomerNbr
    VARCHAR(10) NOT NULL,
    DTVNbr
    INT NOT NULL,
    CalendarWeekNbr
    INT NOT NULL,
    SellinQty
    DECIMAL(5,2) NULL
    )
    GO

    INSERT INTO SOP_Rounding VALUES ( '5062766' , 4565 ,
    200332, .00)
    INSERT INTO SOP_Rounding VALUES ( '5062766' , 4565 ,
    200337, .67)
    INSERT INTO SOP_Rounding VALUES ( '5062766' , 4566 ,
    200337, .33)
    INSERT INTO SOP_Rounding VALUES ( '5062766' , 4567 ,
    200337, NULL)
    INSERT INTO SOP_Rounding VALUES ( '5062766' , 4569 ,
    200337, .00)
    INSERT INTO SOP_Rounding VALUES ( '5062766' , 4565 ,
    200339, .71)
    INSERT INTO SOP_Rounding VALUES ( '5062766' , 4567 ,
    200339, .29)
    INSERT INTO SOP_Rounding VALUES ( '5062766' , 4565 ,
    200341, .50)
    INSERT INTO SOP_Rounding VALUES ( '5062766' , 4566 ,
    200341, .50)
    INSERT INTO SOP_Rounding VALUES ( '5062766' , 4565 ,
    200352, .00)
    GO

    SELECT * FROM SOP_Rounding

    GO
    SELECT '5062766' AS CustomerNbr, 4565 AS DTVNbr, 200332
    AS CalendarWeekNbr, 0 AS SellinQty
    UNION ALL
    SELECT '5062766' AS CustomerNbr, 4565 AS DTVNbr, 200337
    AS CalendarWeekNbr, 1 AS SellinQty
    UNION ALL
    SELECT '5062766' AS CustomerNbr, 4566 AS DTVNbr, 200337
    AS CalendarWeekNbr, 0 AS SellinQty
    UNION ALL
    SELECT '5062766' AS CustomerNbr, 4567 AS DTVNbr, 200337
    AS CalendarWeekNbr, 0 AS SellinQty
    UNION ALL
    SELECT '5062766' AS CustomerNbr, 4569 AS DTVNbr, 200337
    AS CalendarWeekNbr, 0 AS SellinQty
    UNION ALL
    SELECT '5062766' AS CustomerNbr, 4565 AS DTVNbr, 200339
    AS CalendarWeekNbr, 1 AS SellinQty
    UNION ALL
    SELECT '5062766' AS CustomerNbr, 4567 AS DTVNbr, 200339
    AS CalendarWeekNbr, 0 AS SellinQty
    UNION ALL
    SELECT '5062766' AS CustomerNbr, 4565 AS DTVNbr, 200341
    AS CalendarWeekNbr, 1 AS SellinQty
    UNION ALL
    SELECT '5062766' AS CustomerNbr, 4566 AS DTVNbr, 200341
    AS CalendarWeekNbr, 0 AS SellinQty
    UNION ALL
    SELECT '5062766' AS CustomerNbr, 4565 AS DTVNbr, 200352
    AS CalendarWeekNbr, 0 AS SellinQty

    Suresh Guest

  2. #2

    Default Re: Special Rounding

    >> I need to do rounding on numbers but while doing so I
    shouldn't get more/less of the total value. <<

    I think you mean you want to do commerical rounding instead of
    scientific rounding. There are several versions of commerical rounding,
    but they all follow this pattern:

    If the digit to be rounded is (d <= 4) then round down
    If the digit to be rounded is (d >= 6) then round up
    If the digit to be rounded is (d = 5)
    then use a rule that alternates rounding up and rounding down.

    You can write this in SQL with a CASE expression inside a SUM()
    function.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  3. #3

    Default Re: Special Rounding

    Thanks!! Tore & Joe Celko for your responses.

    I was quite helpful in solving the Rounding problem


     
    the rest would have 
    rows should receive 
    values anyway): 
    SellinQtyRounded) 
    Floor(SellinQty) 
    SellinQty DESC 
    a "tiebreaker" sort order 
    may have/need 
    you are in control 
    SellinQtyRounded 
    the NULL will 
    RoundAmt when it doesn't [/ref]
    running 
    >
    >
    >.
    >[/ref]
    Suresh Guest

Similar Threads

  1. rounding
    By GeorgeWS in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: August 10th, 09:05 AM
  2. Rounding off
    By Mike Blezien in forum PERL Beginners
    Replies: 1
    Last Post: November 22nd, 11:22 PM
  3. Rounding Up
    By Brent Bortnick in forum ASP
    Replies: 14
    Last Post: October 8th, 04:19 AM
  4. how to keep sum() from rounding
    By Robert in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 13th, 03:16 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139