Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

18.5. Inventory Costs over Time

The cost of goods in inventory varies over time. Sometimes we can buy low and sell high, and other times the market works against us and the price goes down. This creates a problem in how to compute the cost of the goods sold for any given purchase.

This is easier to explain with a very simple inventory of one kind of item, widgets, to which we add stock once a day. The inventory is then used to fill orders that also come in once a day. The table looks like this:

CREATE TABLE WidgetInventory
(receipt_nbr INTEGER NOT NULL PRIMARY KEY,
 purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
 on_hand_qty INTEGER NOT NULL
   CHECK (on_hand_qty >= 0),
 unit_price DECIMAL (12, 4) NOT NULL);

with the following data: