补货公式oracle sql查询 [英] 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

我想计算补货数量，oracle SQL中正确的公式是什么?

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

示例:
物品代码-ABCD
项目说明-ABCD整个项目
最小数量-20
最大数量-100
再订购数量-20
当前位置的库存-15
主要位置的当前库存-5930

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.

在上面的示例中，我得补货数量为80.

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

谢谢.

推荐答案

这并不是一个Oracle问题，但是我想您是说类似的意思，(插入表名和列名):

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)

也许这也很重要(我在这里真的很疯狂):

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)

这篇关于补货公式oracle sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！