在SQL中跟踪后进先出订单

Question

我正在尝试使用后进先出映射库存，以确定订单最初到达库存的日期与离开的日期。但是，库存可以从正转到负。

例如：

第一天:购买1,000台；(库存1,000台)

第二天:购买1,000台；(库存2,000台)

第三天:销售500台；(库存1500台)

第四天:购买2,000台；(库存3,500台)

第5天:售出3,000台；(库存500台)

第6天:销售10,000台；(库存-9,500台)

我需要知道第5天单位的最小日期是第1天，最大日期是第4天。有没有办法在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

Answer 1

我猜你想要这样的东西

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