how do I round in sql when I want to round to a specific lot size reading from a table

Question

how do I round in sql when I want to round to a specific lot size reading from a table. So If need is 51 but my lot is 50 i want it to come back with 100.

Answer 1

have you tried using case statements? Select CASE WHEN Lot < 50 THEN Lot ELSE 100 END FROM YourTable

This will only work if you have a few different choices for rounding, putting too many case statements would take a lot of time

Answer 2

I am assuming that the "lot size" is a column field name? If so IF statement is probably what you want. A simple example is below

 SELECT IF (lot_size > 49, 100, lot_size) AS lot_size FROM table_name

Answer 3

SELECT 50*CEIL(qty / 50) AS RoundedQty FROM table_name

or if you have lot_size column:

SELECT lot_size*CEIL(qty / lot_size) AS RoundedQty FROM table_name

Answer 4

If you want to round up to the nearest 50, so 1 -> 50, 48 -> 50, 51 -> 100, 87 -> 100, and so on, then

select ((@number div 50) + 1) * 50

where substitute the value you want to round up in place of @number.