replenishment formula oracle sql query

问题:

I have a table with the following structure:

Item Code
item Description
Minimum Quantity
Maximum Quantity
Reorder Quantity
Current Stock in Location
Current Stock in Main Location
Replenishment Quantity

I would like to calculate the replenishment quantity, what would be the correct formula in oracle SQL?

Example:
Item Code - ABCD
item Description - ABCD whole item
Minimum Quantity - 20
Maximum Quantity - 100
Reorder Quantity - 20
Current Stock in Location - 15
Current Stock in Main Location - 5930

from the above, I have to calculate replenishment quantity based on the data that the replenishment quantity will be in increments of reorder quantity but should not exceed maximum quantity and only to be replenished if current stock in location is below minimum quantity and if stock is available in main location.

From the above example, I have to get replenishment quantity as 80.

Thanks in advance.

回答1:

That's not really an Oracle-Question, but I guess you mean something like that, (insert your table and column-names):

select trunc((ma-cu)/re)*re
from(
select 20 mi, 100 ma, 20 re, 15 cu
  from dual)

Edit: Maybe that is also important(I am really wild guessing here):

select least(trunc((ma-cu)/re)*re, trunc(cu_m/re)*re)
from(
select 20 mi, 100 ma, 20 re, 15 cu, 5930 cu_m
  from dual)