I assume that do not have to or want to use
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
13 or ...), you'll get
4,4,3 which add back to
11 and not
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:
qtyordered - (qtyordered - qtyordered / 3) / 2
- qtyordered / 3 AS [First],
(qtyordered - qtyordered / 3) / 2 AS [Second],
qtyordered / 3 AS [Third]
Test at dbfiddle.uk.
The code and the logic behind it is more clear if we use
( SELECT [Third] = (qtyordered) / 3 ) AS q3
( SELECT [Second] = (qtyordered - [Third]) / 2 ) AS q2
( SELECT [First] = (qtyordered - [Third] - [Second]) / 1 ) AS q1
"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