2

I am wanting to divide a int quantity by a number, but my output is not what I am after. This is my DDL

Create Table Orders 
(
    id int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,partid varchar(100) NOT NULL
    ,qtyordered int DEFAULT '0'
    ,orderedby varchar(100) NOT NULL
    ,ordereddate date DEFAULT GETDATE()
)

Insert Into Orders (partid, qtyordered, orderedby) VALUES
('SS100', 10, 'James'), ('RR200', 5, 'Bob'), ('NN300', 3, 'Jake'), ('OO400', 5, 'Blue')

Now I have trired using the SQL Server functions Ceiling() and Floor() but I am not getting my desired output. This is the query I tried, what do I need to do in SQL Server to get an output of 2, 2, 1?

Select
partid
,CEILING(qtyordered/3) As [First] --want to be 2
,CEILING(qtyordered/3) As [Second] --want to be 2
,FLOOR(qtyordered/3) As [Third] --want to be 1
FROM Orders
WHERE partid = 'RR200'
  • And for partid = 'SS100'? What should it return? 4,4,3 or 4,3,3? In other words, should the 3 values add exactly to qtyordered? – ypercubeᵀᴹ Apr 1 '17 at 0:01
6

Due 'qtyordered' is an integer, you need to either, CAST/CONVERT it to decimal, or use a decimal/float value. (3.0)

Select
partid, qtyordered
,CEILING(qtyordered/3.0) As [First] --want to be 2
,CEILING(qtyordered/3.0) As [Second] --want to be 2
,FLOOR(qtyordered/3) As [Third] --want to be 1
FROM Orders
WHERE partid = 'RR200'
GO
partid | qtyordered | First | Second | Third
:----- | ---------: | :---- | :----- | ----:
RR200  |          5 | 2     | 2      |     1

dbfiddle here

According to MS DOCS, CEILING returns same type as the numeric expresion:

Return Types
Returns the same type as numeric_expression

  • 1
    @BellHopByDayAmetuerCoderByNigh - Why? Because if all the terms in the equation are integers, SQL will return an integer result. And CEILING(1) = 1. Make sure at least one value has a digit right of the decimal point, and SQL won't pre-force the result to integer for you. – RDFozz Mar 31 '17 at 23:58
  • @RDFozz you're right, edited. – McNets Apr 1 '17 at 0:07
4

I assume that do not have to or want to use CEILING and FLOOR but the aim is to divide the qtyordered value into 3 integer values - that are as close to each other as possible - and that when you then add them you'll get back the original value.

While for value 5 the ceiling-ceiling-floor methods works ok, if qtyordered is 10 (or 4 or 7 or 13 or ...), you'll get 4,4,3 which add back to 11 and not 10!

You can achieve consistent (mathematically) results using only integer arithmetic. The trick is to start from the "third" value, i.e. the value that will be the smallest of the three:

   "Third"  =  qtyordered / 3

then subtract that value from qtyordered and divide by 2:

   "Second" = (qtyordered - "Third") / 2

and then subtract both "Third" and "Second" from qtyordered to find the "First":

   "First"  = qtyordered - "Second" - "Third"  

The query becomes:

SELECT 
   partid,
   qtyordered,
   qtyordered - (qtyordered - qtyordered / 3) / 2   
                            - qtyordered / 3        AS [First], 
                (qtyordered - qtyordered / 3) / 2   AS [Second],
                              qtyordered / 3        AS [Third] 
FROM 
    Orders ;

Test at dbfiddle.uk.

The code and the logic behind it is more clear if we use CROSS APPLY:

SELECT 
   partid,
   qtyordered,
   [First], 
   [Second],
   [Third] 
FROM 
    Orders 
  CROSS APPLY
    ( SELECT [Third]  = (qtyordered)                      / 3 )  AS q3
  CROSS APPLY
    ( SELECT [Second] = (qtyordered - [Third])            / 2 )  AS q2
  CROSS APPLY
    ( SELECT [First]  = (qtyordered - [Third] - [Second]) / 1 )  AS q1
 ;

The expression "First" = qtyordered - "Second" - "Third" assures that the 3 values will add up to the original qtyordered. Combined with the other 2 expressions, it's easy to show that:

"Third" <= "Second" <= "First"

and that the difference "First" - "Third" is either 1 or 0, so the three values are as close as possible to 1/3 or qtyordered.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.