Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    9

    Unanswered: Roundup to nearest x

    Morning All,

    I have a SQL statement that returns two columns. I want to add a further column which rounds UP the CURRENTQTY to the nearest multiple of packetsize.

    Example Table:

    Table.CURRENTQTY, Table.PACKETSIZE.
    Some data would be

    CURRENTQTY PACKETSIZE
    167 28
    5 2
    200 10
    11 1
    12 10
    My expected results would be;
    CURRENTQTY PACKETSIZE, ROUNDED
    167 28 168
    5 2 6
    200 10 200
    11 1 11
    12 10 20
    Can anyone advise how i would achieve this?

    Thanks
    Sam

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Code:
    use tempdb;
    go
    
    create table Units (
    	id int,
    	packetsize int
    );
    
    create table Orders (
    	id int,
    	qty int
    );
    
    insert into Units values
    	(1,28),
    	(2,2),
    	(3,10);
    	
    insert into Orders values
    	(1,167),
    	(2,5),
    	(3,200);
    	
    select
    	Orders.qty,
    	Units.packetsize,
    	(Orders.qty/Units.packetsize+case when Orders.qty%Units.packetsize > 0 then 1 else 0 end)*Units.packetsize
    from
    	Orders
    		inner join
    	Units on
    			Orders.id = Units.id
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    May 2009
    Posts
    9
    Thanks thats fantastic in the whole except i have a few strange figures.

    QTY, PACKETSIZE, ROUNDED
    109.555550 500 10500
    94.555550 150 1650
    57.688885 28 616
    Sam

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try:
    Code:
    SELECT CURRENTQTY, 
    	PACKETSIZE,
    	CEILING(CURRENTQTY * 1.0/ PACKETSIZE) * PACKETSIZE
    FROM #DatTable
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    May 2009
    Posts
    9
    Still reporting some strange figures, it only happens when my QTY column is greater than or equal to .5 so 10.49 would cause an issue but 10.44 would not.

    Sam

  6. #6
    Join Date
    May 2009
    Posts
    9
    Thanks Wim/Roac, i found the resolution by finding the CEILING() in my QTY before rounding to packetsize.

    Sam

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by samtwilliams View Post
    Still reporting some strange figures, it only happens when my QTY column is greater than or equal to .5 so 10.49 would cause an issue but 10.44 would not.
    You mean CURRENTQTY is not an integer number? That fact was not mentioned in the first post or made clear in the examples.

    Don't forget that no-one on this list, except you, know what your problem is and what your data model looks like. If those things are not made clear to us, we won't be able to give you a solution that you can actually use.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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