Joe Celko's SQL for Smarties, 3rd Edition

19.5. FIFO and LIFO Subsets

This will be easier to explain with an example for readers who have not worked with an Inventory system before. Imagine that we have a warehouse of one product to which we add stock once a day.

CREATE TABLE InventoryReceipts
(receipt_nbr INTEGER PRIMARY KEY,
 purchase_date DATETIME NOT NULL,
 qty_on_hand INTEGER NOT NULL
   CHECK (qty_on_hand >= 0),
 unit_price DECIMAL (12,4) NOT NULL);

Let’s use this sample data for discussion.

InventoryReceipts
receipt_nbr purchase_date qty_on_hand unit_price
========================================
 1          '2006-01-01'   15     10.00
 2          '2006-01-02'   25     12.00
 3          '2006-01-03'   40     13.00
 4          '2006-01-04'   35     12.00
 5          '2006-01-05'   45     10.00

The business now sells 100 units on 2006-01-05. How do you calculate ...