O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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:

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required