Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Of Interest » Articles of Interest » FIFO and LIFO - Part 1
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 4726
 

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)
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
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone