时间戳

首页 > 解决方案 > Tracking LIFO Orders in SQL

sql - Tracking LIFO Orders in SQL

问题描述

I am trying to map inventory using LIFO to determine the dates the orders initially arrived in the inventory to the day that they leave. However, the inventory can go from positive to negative.

For example:

Day 1: purchase 1,000 units; (inventory 1,000 units)

Day 2: purchase 1,000 units; (inventory 2,000 units)

Day 3: sell 500 units; (inventory 1,500 units)

Day 4: purchase 2,000 units; (inventory 3,500 units)

Day 5: sell 3,000 units; (inventory 500 units)

Day 6: sell 10,000 units; (inventory -9,500 units)

I will need to know that Day 5 units come from a minimum date of day 1 and maximum date of day 4. Is there any way to do this in SQL?

    UPDATE #TEMP_ORDERS_STEP_2
    SET CUMULATIVE_UNITS = UNITS
    , REMAINING_UNITS = UNITS
    , Min_Inventory_Date = 'n/a'
    , Max_Inventory_Date = 'n/a'
    WHERE Row_ID = 1
    AND CUMULATIVE_SHARES IS NULL
    --(30609 row(s) affected)

    SELECT DateId, OrderID, ProductCode, ProductType, Units, Row_ID, Inventory, CUMULATIVE_UNITS, Min_Inventory_Date, Max_Inventory_Date
    FROM #TEMP_ORDERS_STEP_2 A
    JOIN (SELECT * FROM #TEMP_ORDERS_STEP_2 WHERE REMAINING_UNITS IS NOT NULL) B
        ON A.ProductCode = B.ProductCode AND A.ProductType = B.ProductType AND A.Row_ID = B.Row_ID + 1  
    WHERE A.CUMULATIVE_SHARES IS NULL

标签： sqlinventorylifo

解决方案

我猜你想要这样的东西

with hist as (select *
from (
values (1 , 1000 , 0),
(2 , 1000 , 0),
(3 , 0 , 500),
(4 , 2000 , 0),
(5 , 0 , 3000),
(6 , 0 , 10000)
) as V (day, buy, sell)),

stock as (
select day, 
sum(buy) over(partition by 0 order by day ROWS UNBOUNDED PRECEDING)
- sum(sell) over(partition by 0 order by day ROWS UNBOUNDED PRECEDING) as stock
from hist),

stock_with_max_min_days as (
select s.day, s.stock,
FIRST_VALUE(s2.day) over(partition by s.day order by s2.stock asc ROWS UNBOUNDED PRECEDING) min_previous_day,
FIRST_VALUE(s2.day) over(partition by s.day order by s2.stock desc ROWS UNBOUNDED PRECEDING) max_previous_day
from stock s
left outer join stock s2
on s.day > s2.day)

select day, stock, min_previous_day, max_previous_day
from stock_with_max_min_days
group by day, stock, min_previous_day, max_previous_day

你可以在这个小提琴中看到一个工作演示：

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=76c61fbd3bcc1a0c048587601ee2b1c0

推荐阅读