# FIFO and LIFO - Part 1

**Part 1 | Part 2**

I am going to start this article by setting up a very simple inventory of one kind of item, into which we add stock once a day. The “in” inventory is then used to fill orders that also come in once a day. Here is a minimal table for getting:

CREATE TABLE WidgetInventory

(receipt_nbr INTEGER NOT NULL PRIMARY KEY,

purchase_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,

qty_on_hand INTEGER NOT NULL

CHECK (qty_on_hand >= 0),

unit_price DECIMAL (12,4) NOT NULL);

with the following data:

WidgetInventory

receipt_nbr purchase_date qty_on_hand unit_price

================================================

1 '2005-08-01' 15 10.00

2 '2005-08-02' 25 12.00

3 '2005-08-03' 40 13.00

4 '2005-08-04' 35 12.00

5 '2005-08-05' 45 10.00

The business now sells 100 units on 2005-08-05. How do you calculate the value of the stock sold? There is not one right answer, but here are some options:

- Use the current replacement cost, which is $10.00 per unit as of 2005-08-05. That would mean the sale cost us only $1000.00 because of a recent price break.
- Use the current average price per unit. We have a total of 160 units, for which we paid a total of $1840.00; that gives us an average cost of $11.50 per unit, or $1150.00 in total inventory costs.
- Use LIFO, which stands for, “Last In, First Out.” We start by looking at the most recent purchases and work backwards through time.
- Use FIFO, which stands for “First In, First Out.” We start by looking at the earliest purchases and work forward through time.

2005-08-05: 45 * $10.00 = $450.00 and 45 units

2005-08-04: 35 * $12.00 = $420.00 and 80 units

2005-08-03: 20 * $13.00 = $260.00 and 100 with 20 units left over

for a total of $1130.00 in inventory cost.

2005-08-01: 15 * $10.00 = $150.00 and 15 units

2005-08-02: 25 * $12.00 = $300.00 and 40 units

2005-08-03: 40 * $13.00 = $520.00 and 80 units

2005-08-04: 20 * $12.00 = $240.00 with 15 units left over

for a total of $1210.00 in inventory costs.

The first two scenarios are trivial to program.

CREATE VIEW (current_replacement_cost)

AS

SELECT unit_price

FROM WidgetInventory

WHERE purchase_date

= (SELECT MAX(purchase_date) FROM WidgetInventory);

CREATE VIEW (average_replacement_cost)

AS

SELECT SUM(unit_price * qty_on_hand)/SUM(qty_on_hand)

FROM WidgetInventory;

The LIFO and FIFO are more interesting because they involve looking at matching the order against blocks of inventory in a particular order. Consider this view:

CREATE VIEW LIFO (stock_date, unit_price, tot_qty_on_hand, tot_cost)

AS

SELECT W1.purchase_date, W1.unit_price, SUM(W2.qty_on_hand), SUM(W2.qty_on_hand *

W2.unit_price)

FROM WidgetInventory AS W1,

WidgetInventory AS W2

WHERE W2.purchase_date <= W1.purchase_date

GROUP BY W1.purchase_date, W1.unit_price;

A row in this view tells us the total quantity on hand, the total cost of the goods in inventory, and what we were paying for items on each date. The quantity on hand is a running total. We can get the LIFO cost with this query:

SELECT (tot_cost - ((tot_qty_on_hand - :order_qty) * unit_price))

AS cost

FROM LIFO AS L1

WHERE stock_date

= (SELECT MIN(stock_date)

FROM LIFO AS L2

WHERE tot_qty_on_hand >= :order_qty);

This is straight algebra and a little logic. You need to find the most recent date when we had enough (or more) quantity on hand to meet the order. If, by dumb blind luck, there is a day when the quantity on hand exactly matched the order, return the total cost as the answer. If the order was for more than we have in stock, then return nothing. If we go back to a day when we had more in stock than the order was for, look at the unit price on that day, multiply by the overage, and subtract it.

Alternatively, you can use a derived table and a CASE expression. The CASE expression computes the cost of units that have a running total quantity less than the :order_qty and then performs algebra on the final block of inventory, which would put the running total over the limit. The outer query does a sum on these blocks:

SELECT SUM(W3.v) AS cost

FROM (SELECT W1.unit_price

* CASE WHEN SUM(W2.qty_on_hand) <= :order_qty

THEN W1.qty_on_hand

ELSE :order_qty

- (SUM(W2.qty_on_hand) - W1.qty_on_hand)

END

FROM WidgetInventory AS W1,

WidgetInventory AS W2

WHERE W1.purchase_date <= W2.purchase_date

GROUP BY W1.purchase_date, W1.qty_on_hand, W1.unit_price

HAVING (SUM(W2.qty_on_hand) - W1.qty_on_hand) <= :order_qty)

AS W3(v);

FIFO can be found with a similar VIEW or derived table:

CREATE VIEW FIFO (stock_date, unit_price, tot_qty_on_hand, tot_cost)

AS

SELECT W1.purchase_date, W1.unit_price,

SUM(W2.qty_on_hand), SUM(W2.qty_on_hand *

W2.unit_price)

FROM WidgetInventory AS W1,

WidgetInventory AS W2

WHERE W2.purchase_date <= W1.purchase_date

GROUP BY W1.purchase_date, W1.unit_price;

with the corresponding query:

SELECT (tot_cost - ((tot_qty_on_hand - :order_qty) * unit_price)) AS cost

FROM FIFO AS F1

WHERE stock_date

= (SELECT MIN (stock_date)

FROM FIFO AS F2

WHERE tot_qty_on_hand >= :order_qty);

These queries and VIEWs only told us what is the value of the Widget inventory. Notice that we never actually shipped anything from the inventory. How to write the UPDATE statements is the topic for the next article in this series.

--

**Joe Celko**** **was
a member of the ANSI X3H2 Database Standards Committee and helped write
the SQL-92 standards. He is the author of over 750 magazine columns and
seven books, the best known of which is *SQL for Smarties*, now in its third edition. For more articles, puzzles, resources, and more, go to www.celko.com.

Contributors : Joe Celko

Last modified 2006-01-06 10:43 AM