Tracking LIFO Orders in SQL

Question

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

Out of curiosity, is there a reason you're using an RDBMS instead of a queue server for this? (e.g. MSMQ, RabbitMQ, Kafka, etc)? — Dai, Dec 12 '19 at 00:30
@Dai this is a common RDBMS implementation, tracking orders and inventory. You could achieve elements of the same thing in a managed queue service but I suspect there is a lot more to this application that means management and tracking of this type of data in an RDBMS more than appropriate. — Chris Schaller, Dec 12 '19 at 00:34

score 0 · Answer 1 · answered Dec 12 '19 at 02:10

I guess you want something like this

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

you can see a working demo in this fiddle:

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

Tracking LIFO Orders in SQL

1 Answers1