# DBAzine.com

##### Personal tools
You are here: Home » FIFO and LIFO - Part 1
Seeking new owner for this high-traffic DBAzine.com site.
##### Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]

# FIFO and LIFO - Part 1

by Joe Celko

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:

1. 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.
2. 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.
3. Use LIFO, which stands for, “Last In, First Out.” We start by looking at the most recent purchases and work backwards through time.
4. ` 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.

5. Use FIFO, which stands for “First In, First Out.” We start by looking at the earliest purchases and work forward through time.
6. ` 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)ASSELECT unit_price  FROM WidgetInventory  WHERE purchase_date        = (SELECT MAX(purchase_date) FROM WidgetInventory);CREATE VIEW (average_replacement_cost)ASSELECT 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)ASSELECT 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)ASSELECT 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