先进先出（FIFO）库存成本核算法

Question

这是一篇我在项目中发现有用的有趣文章：

基于集合的速度狂人：FIFO库存SQL问题：

我们使用的库存表来跟踪库存进出仓库的移动。我们的仓库最初是空的，随后由于库存购买（tranCode ='IN'）或由于随后的退货（tranCode ='RET'）而进入仓库，并且当库存销售时从仓库中移出（tranCode ='OUT'）。每种类型的库存项都由ArticleID标识。由于给定项目的购买、销售或退货而导致的每次库存进出仓库的移动都会在Stock表中添加一行，由StockID标识唯一地标识，并描述添加或删除的物品数量、购买价格、交易日期等。

尽管我正在我的进行中的项目中使用它，但我无法确定如何获取每个“OUT”事务的价格。我需要此值以确定我将向客户收取多少费用。

1. 首先添加5个苹果（每个$10.00）到库存中，总共为$50.00

2. 添加3个苹果（每个$20.00）到库存总共为8个苹果，总价格为$110.00

3. 然后取出6件物品（每个$10.00和每个$20.00的5个），总计$70

4. 交易后将留下2个苹果@$20每个，总共$40

---

 Here's my current table
 Item    transaction code    qty     price   
 apple   IN                    5     10.00    
 apple   IN                    3     20.00   
 apple   OUT                   6          

 Manual computation for the OUT transaction price (FIFO)
 QTY     price   total price 
 5       10.00   50.00 
 1       20.00   20.00 
 TOTAL:6         70.00 

 Output of the script:
 Item    CurrentItems   CurrentValue
 apple   2            40.00

 What I need:
 Item    transaction code    qty     price   CurrentItems    CurrentValue 
 apple   IN                    5     10.00   5               50.00 
 apple   IN                    3     20.00   8               110.00 
 apple   OUT                   6             2                   40.00 

 This too will be OK
 Item    transaction code    qty     price   CurrentItems    
 apple   IN                    5     10.00   0               
 apple   IN                    3     20.00   0                
 apple   OUT                   6         70 

获得比赛胜出的脚本非常有用，我希望有人能帮助我了解如何获得“OUT”交易的价格。

- samantha07

---

你需要为每个交易（进货、出货、退货）分配一个唯一的编号，以便你可以知道苹果的数量和价格。基于此，你可以通过计算（数量*价格）来计算总共花费了多少钱。 - Alpesh Prajapati

股票表中有一个主键。只是为了简单起见，我不想显示它，我也使用ItemID代替了“apple”这个词。你不能仅仅通过(qty * price)来计算价格，记住我需要实现FIFO规则。 - samantha07

Answer 1

我建议按照以下方式设计您的表格： 在您的表格中添加一个新字段，即qty_out

出售前的表格：

Item transaction code    qty     qty_out  price   
 apple   IN                    5    0        10.00    
 apple   IN                    3    0        20.00   
 apple   OUT                   6    null

在售出6件物品后的表格：

Item    transaction code    qty     qty_out  price   
 apple   IN                    5    5        10.00    
 apple   IN                    3    1        20.00   
 apple   OUT                   6    null

你可以将 "qty"（进货）和 "qty_out"（销售）进行比较，以找出价格。

- saeed khalafinejad

Answer 2

请看下面的TSQL代码。基本思路是：

1. 对于每一行销售记录，假设数量为Qty，计算当前行之前的累计销售额，称为Previous_Sold。

2. 对于步骤1中的每一行销售记录，在所有之前的购买记录中计算截至该购买记录时的累计库存量，称为Previous_Running_Stock。

3. 对于步骤2中的购买记录，计算

Open_Stock = Previous_Running_Stock - Previous_Sold

Close_stock = Previous_Running_Stock - Previous_Sold - Qty.

4. 筛选并仅保留购买记录，如果

open_stock >0，表示有足够的库存来满足销售订单

close_stock < 0，表示购买记录中的库存已全部用完，或者最早（第一行）的close_stock >= 0，表示从该行购买的部分已使用。

5. 聚合（求和乘积）价格和数量以得到步骤4中的LIFO成本。

我相信它可以很容易地修改为LIFO和平均成本。

--initial table of trades
item       item_trade_order     direction  unit_price    qty
Apple      1                    buy        10            100
Apple      2                    buy        9             150
Blueberry  1                    buy        5             300   
Apple      3                    sell       12            50
Apple      4                    buy        11            200
Apple      5                    sell       10            350
Blueberry  2                    sell       10            50


--code, using CTE


; with step1 as
(
    select *
        , coalesce(sum(case direction when 'sell' then 1 else 0 end * qty) over(partition by item order by item_order rows between unbounded preceding and 1 preceding), 0) Previous_Sold
    from trade 
)
, step2_3 as
(
    select *
        , Previous_running_stock - Previous_Sold Open_Stock
        , Previous_running_stock - Previous_Sold - qty Close_Stock
        , ROW_NUMBER() over(partition by item, item_order order by (case when Previous_running_stock - Previous_Sold - qty < 0  then null else 0 - item_order end) desc) rnk
    from step1 t1
    cross apply
    (
        select item_order batch_order, price batch_prc, qty batch_qty
            , sum(qty) over(order by item_order rows unbounded preceding) Previous_running_stock
        from trade
        where direction = 'buy'
        and item = t1.item
        and item_order < t1.item_order
    ) batch
    where t1.direction = 'sell'
)
, step4 as
(
    select *
    from step2_3
    where Open_Stock > 0
    and (Close_Stock < 0 or rnk = 1)
)
select item, item_order, direction, AVG(price) prc, AVG(qty) qty
    ,   sum(case when Close_Stock > 0 then batch_qty - close_stock else case when open_stock < batch_qty then open_stock else batch_qty end end * Batch_Prc) / nullif(avg(qty), 0) FifoUnitCost
from step4
group by item, item_order, direction
order by item, item_order

- robotj

Answer 3

根据这篇文章，脚本得到的结果是库存的价值。你需要修改它，使其只计算前N个库存，而不是所有库存。

我建议使用CASE语句，在检查运行总数时设置每个“IN”中的项目数量，因为你知道库存项目和要取出的数量。

- Dale M

Answer 4

你无法跟踪每个 OUT 交易本身，但是你可以通过获取最后一个（除了你要计算的那个）IN 或 OUT 行及其当前值列，并减去你要计算的当前值来计算它。

在这个例子中

StockID  ArticleID  TranDate  TranCode  Items    Price    CurrentItems  CurrentValue
4567     10000      10:45:07  IN          738   245.94             738    181,503.72
21628    10000      12:05:25  OUT         600                      138     33,939.72
22571    10000      14:39:27  IN           62   199.95             200     46,336.62
30263    10000      16:14:13  OUT         165                       35      6,998.25
42090    10000      18:18:58  RET           5                       40      7,998.00
53143    10000      20:18:54  IN          500   135.91             540     75,953.00

对于交易30263，价格将为46,336.62 - 6,998.25 = 39,338.37

- gev911

Answer 5

如何构建一个表格，每个产品项都有一行，因此每个苹果都插入了其价格和可用性（未售出/已售出）的行。
然后，您可以选择前n个项目，并与您想要的每个产品相关联的价格。本质上，您只是创建了一个项目队列，并从队列的前面（具有最早插入日期的）删除“未售出”的项目。

- Matt Spinks

---

谢谢。这正是我的表格正在做的事情。也许我没有理解你的意思，你能给我提供一个例子吗？ - samantha07