先入先出（FIFO）的库存成本(First-in-first-out (FIFO) inventor

Here's an interesting article that I found useful on my project:

Set-based Speed Phreakery: The FIFO Stock Inventory SQL Problem:

Stock table which we use to track the track movements of stock in and out of our imaginary stock warehouse. Our warehouse is initially empty, and stock then moves into the warehouse as a result of a stock purchase (tranCode = 'IN'), or due to a subsequent return (tranCode = 'RET'), and stock moves out of the warehouse when it is sold (tranCode = 'OUT'). Each type of stock tem is indentified by an ArticleID. Each movement of stock in or out of the warehouse, due to a purchase, sale or return of a given item, results in a row being added to the Stock table, uniquely identified by the value in the StockID identity column, and describing how many items were added or removed, the price for purchases, the date of the transaction, and so on.

Though I'm using this on my on-going project, Im stuck on how to get the price-to-charge on every transaction 'OUT'. I need to have this value to determine how much i will charge my customers.

1. First add 5 apples (each $10.00) to the stock, for a total of $50.00

2. Add 3 apples (each $20.00) to the stock total of 8 apples, for a total price of $110.00

3. Then take out 6 items (5 each $10.00 and 1 each $20.00) $70 total

4. After the transaction it will be leaving 2 apples @$20 each with a total of $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 

The script posted that won the competition was very useful, I hope someone can help me on how to get the price per 'OUT' transaction

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_out”（用于交易），找出价格。

Answer 2:

如何构建具有每个产品项目一排桌子，所以插入每个苹果一行连同它的价格和可用性（未售出/销售）。
然后，你可以选择前n项，与每一个你想要的产品的相关价格。 从本质上讲，你只是创建项目的队列，并删除那些队列从正面（从最早的插入日期）“流拍”的人。

Answer 3:

根据这篇文章，结果该脚本得到的是存货的价值。 你会需要修改这个以便不用计算所有的库存，你只用前N项。

如您核对运行总，因为你知道库存物品，你要拿出多少我会建议一个CASE语句从每个“IN”设置的项目数量。

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

Answer 5:

请参阅TSQL下面的代码。 其基本思想是

1. 对于每卖出行，说数量为数量，计算运行总销售额前的当前行，把它Previous_Sold。

2. 步骤1中每卖出行，找到以前所有买行和计算运行总存量由那买的，称之为Previous_Running_Stock。

3. 对于在步骤2中购买的行，计算

Open_Stock = Previous_Running_Stock - Previous_Sold

Close_stock = Previous_Running_Stock - Previous_Sold - 数量。

4. 过滤器只保留购买行如果

open_stock> 0，意思是有足够的库存来填补卖出指令

和close_stock <0，从购买行意库存全部用完，或最早的（第一行），其中close_stock> = 0，这意味着从该行购买被部分使用。

5. 骨料价格和数量（的乘积和的），以获得LIFO成本在步骤4。

我相信它可以很容易地修改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

文章来源: First-in-first-out (FIFO) inventory costing