Оценка стоимости запасов в порядке поступления (FIFO)

Вот интересная статья, которая показалась мне полезной для моего проекта:

Скорость Phreakery на основе множеств: проблема SQL инвентаризации FIFO:

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.

Хотя я использую это в своем текущем проекте, я застрял на том, как получить цену, взимаемую с каждой транзакции "OUT". Мне нужно иметь это значение, чтобы определить, сколько я буду взимать с моих клиентов.

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

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

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

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 

Размещенный сценарий, который выиграл конкурс, был очень полезен, я надеюсь, что кто-нибудь может помочь мне узнать, как получить цену за «OUT». сделка

 samantha0727 июн. 2012 г., 06:58
Это первичный ключ в таблице запасов. просто я не хотел показывать это для простоты, я также использовал ItemID вместо слова apple. Вы не можете просто рассчитать цену только по (кол-во * цене), помните, что мне нужно реализовать правило FIFO.
 Alpesh Prajapati26 июн. 2012 г., 12:09
вам нужно дать каждой транзакции (In, Out, ret) уникальный номер, чтобы вы могли знать кол-во и цену яблок. На основании этого вы можете рассчитать, сколько всего было вычислено (кол-во * цена)

Ответы на вопрос(5)

Как насчет построения таблицы, в которой есть строка для каждой позиции продукта, поэтому вставляется строка для каждого яблока вместе с его ценой и доступностью (не продано / продано).
Затем вы можете просто выбрать лучшие n товаров с ценой, связанной с каждым из продуктов, которые вы хотите. По сути, вы просто создаете очередь предметов и удаляете те, которые являются "непроданными". с начала (с самой старой датой вставки) очереди.

 samantha0727 июн. 2012 г., 06:56
Благодарю. это именно то, что делает мой стол. может я не понял твою точку зрения, можешь привести пример?

Основываясь на статье, результатом, который получил скрипт, была стоимость инвентаря. Вам нужно будет изменить это так, чтобы вместо расчета для всего инвентаря вы использовали только первые N предметов.

Я бы предложил инструкцию CASE, чтобы установить количество элементов из каждого «IN». как вы проверяете по текущему итогу, так как вы знаете предметы инвентаря и номер, который вы хотите взять.

Я предлагаю оформить ваш стол, как показано ниже: Добавьте новое поле в вашу таблицу, то есть 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

Вы можете сравнить & quot; кол-во & quot; с & quot; qty_out & quot; (для транзакций IN), чтобы узнать цену.

Вы не можете отслеживать каждую 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 436,62 - 6 998,25 =39,338.37

Смотрите код ниже в TSQL. Основная идея

for each sell row, say the quantity is Qty, calculate running total sales PRIOR to the current row, call it Previous_Sold.

for each sell row in step 1, find all PREVIOUS buy rows and calculate running total stock UP TO that buy, call it Previous_Running_Stock.

for buy rows in step 2, calculate

Open_Stock = Previous_Running_Stock - Previous_Sold

Close_stock = Previous_Running_Stock - Previous_Sold - Кол-во.

Filter and only keep buy rows if

open_stock & gt; 0, что означает, что запаса достаточно для выполнения заказа на продажу

и close_stock & lt; 0 означает, что акции из строки покупки полностью израсходованы, или самые ранние (первая строка), где close_stock & gt; = 0, что означает, что покупка из этой строки используется частично.

aggregate (sum product of) price and quantity to get LIFO cost in step 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) q,ty
    ,   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

Ваш ответ на вопрос