Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@hidesroom

Visualize SQL | LIFO-FIFO INVENTORY

Last updated at Posted at 2024-10-27

Beginning

This article is based on 『SQL Puzzle and Answers』
　⇒ #59 LIFO-FIFO INVENTORY
It is better with this book to read this article.

you have heard of『FIFO & LIFO』at school or somewhere
・・ I think

LIFO(Last In, First Out)
⇒ (e.g)
⇒ the most recently purchased goods are sold first

FIFO(First In, First Out)
⇒ (e.g)
⇒ older inventory is the first to be sold

▼ image of LIFO & FIFO
re_0.png

what you are going to do is ・・
to calculate the purchase cost of sold units

Let's say
you sold 100 units

There are two ways to caluculate the purchase cost
from older purchase cost(LIFO)
or
from latest purchase cost(FIFO)

but you need to be careful
　⇒ Not purchasing 100 units on the same day
　⇒ so you can say
　　A total of 100 purchase price is
　　sum of『different purchase unit price』

Let's get started !!

Table & Data

SQL
-- Inventory Table
CREATE TABLE WidgetInventory(
  receipt_nbr INTEGER NOT NULL PRIMARY KEY,
  purchase_date DATE NOT NULL,
  qty_on_hand INTEGER NOT NULL CHECK (qty_on_hand >= 0),
  unit_price DECIMAL (5,2) NOT NULL
);

 -- receipt_nbr   : receipt number
 -- purchase_date : purchase date
 -- qty_on_hand   : purchase quantity on that day
 -- unit_price    : purchase price

INSERT INTO WidgetInventory VALUES(1, '2005-08-01', 15, 10.00);
INSERT INTO WidgetInventory VALUES(2, '2005-08-02', 25, 12.00);
INSERT INTO WidgetInventory VALUES(3, '2005-08-03', 40, 13.00);
INSERT INTO WidgetInventory VALUES(4, '2005-08-04', 35, 12.00);
INSERT INTO WidgetInventory VALUES(5, '2005-08-05', 45, 10.00);

-- show table
SELECT * FROM WidgetInventory;

▼ Output

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

from this table ・・
calculate purchase price of the sold 100 units
　① from the top of the purchase date(blue)
　② from the bottom of the purchase date(orange)

FIFO
　⇒ extract the purchase date from the top
　⇒ chronological order(blue)
　⇒ exceed 100 on 2005-08-04

LIFO
　⇒ extract the purchase date from the bottom
　⇒ reverse chronological order(orange)
　⇒ exceed 100 on 2005-08-03

Calculate the purchase price
by identifying the day when the total unit hits to 100
is the interesting part of this puzzle

▼ image of 『total unit』 hits to 100
re_1.png

Creating Source Data①(LIFO)

SQL
SELECT W1.*, W2.*									
FROM WidgetInventory AS W1, WidgetInventory AS W2

First of all・・・
making all combinations of
the WidgetInventory table using CROSS JOIN

since the WidgetInventory table has 5 rows
25(5 x 5) rows will be created

edit this output and make it fit to LIFO format
　⇒ extract rows that are clear numbers below image

▼ Output
re_2.png

Creating Source Data②(LIFO)

SQL
SELECT W1.*, W2.*									
FROM WidgetInventory AS W1, WidgetInventory AS W2	
WHERE W1.purchase_date <= W2.purchase_date

filter the output using two purchase_date
　⇒ W1.purchase_date <= W2.purchase_date
　⇒ W2.purchase_date is the same as
　　or later than W1.purchase_date
　⇒ 15 rows left

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-01
　⇒ All W2.purchase_date match the condition

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-02
　⇒ four W2.purchase_date match the condition
　⇒ check image below

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-04
　⇒ two W2.purchase_date match the condition
　⇒ check image below

you might think what is this doing(・_・?)

you will see later

▼ Output(left side of image)
re_3.png

making combinations is one of the techniques
often seen in this puzzle

check the article below

Edit Source Data①(LIFO)

SQL
SELECT
  W1.purchase_date AS stock_date, 
  W1.unit_price AS unit_price,
  SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
  SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
FROM WidgetInventory AS W1, WidgetInventory AS W2
WHERE W1.purchase_date <= W2.purchase_date
GROUP BY W1.purchase_date, W1.unit_price

ORDER BY W1.purchase_date

▼ Output

purchase_date unit_price tot_qty_on_hand tot_cost
2005-08-01 10 160 1840.00
2005-08-02 12 145 1690.00
2005-08-03 13 120 1390.00
2005-08-04 12 80 870.00
2005-08-05 10 45 450.00

Let's see this query in detil
check 『Creating Source Data②(LIFO)』again

GROUP BY (purchase_date & unit_price) on the left side
then, you can calculate 『purchase price and quantity』
using SUM on the right side columns
　⇒ check the blue part of the below image

what you have done in『Creating Source Data②(LIFO)』was

preparing to SUM(purchase price and quantity)
by JOIN W2.purchase_date later than W1.purchase_date

▼ preparing to SUM(purchase price and quantity)
re_4.png

Calculate past purchase price
　⇒ check below image

purchase_date：2005-08-05
　⇒ unit price：10
　⇒ 45 pieces in stock
　　purchase price：450(10 x 45)
～～～～～～～～～～～～～～～～～～～～～～
purchase_date：2005-08-04
　⇒ unit price：12
　⇒ 35 pieces in stock
　　purchase price：420(12 x 35)

　⇒ added up with 2005-08-05
　　80 pieces in stock
　　purchase price：870
～～～～～～～～～～～～～～～～～～～～～～
purchase_date：2005-08-03
　⇒ unit price：13
　⇒ 40 pieces in stock
　　purchase price：520(13 x 40)

　⇒ added up with 2005-08-04 and 2005-08-05
　　120 pieces in stock
　　purchase price：1390

・・・ do the same way ~

▼ Calculate past purchase price
re_5.png

Can you see the whole picture of this puzzle・・ ？？
but what you want is the purchase price for 100 units

purchase_date：2005-08-04
⇒ 80 pieces in stock

purchase_date：2005-08-03
⇒ 120 pieces in stock

It takes a little more work to take out just 100 units
the next job is ・・
taking out the row of purchase_date(2005-08-03) and minus 20

Edit Source Data②(LIFO)

SQL
WITH LIFO AS (
  SELECT 
    W1.purchase_date AS stock_date, 
    W1.unit_price AS unit_price,
    SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
    SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
  FROM WidgetInventory AS W1, WidgetInventory AS W2
  WHERE W1.purchase_date <= W2.purchase_date
  GROUP BY W1.purchase_date, W1.unit_price
)

SELECT 
 L1.*,
 (SELECT MAX(stock_date) FROM LIFO AS L2 
  WHERE tot_qty_on_hand >= 100) AS max_date
FROM LIFO AS L1

▼ Output (add：max_date)

purchase_date unit_price tot_qty_on_hand tot_cost max_date
2005-08-01 10 160 1840.00 2005-08-03
2005-08-02 12 145 1690.00 2005-08-03
2005-08-03 13 120 1390.00 2005-08-03
2005-08-04 12 80 870.00 2005-08-03
2005-08-05 10 45 450.00 2005-08-03

Rewrite 『Edit Source Data①(LIFO)』
　⇒ write using CTE, its name is LIFO

max_date column is added in the SELECT statement for checking
　⇒ stock_date when total inventory is over 100 units ・・
　⇒ there are a few days
　　 2005-08-01,2005-08-02 and 2005-08-03
　⇒ choose the latest date
　　 MAX(stock_date)
　　 2005-08-03

use MAX() function to choose the date
because you are handling LIFO data here

In the next FIFO, function will be MIN()

▼ taking out purchase_date(2005-08-03)
re_5.png

the last jos is ・・・

by subtracting 20 units
from the total inventory held on 2005-08-03
then, you can calculate the purchase price for 100 units

▼ purchase price for 100 units
re_6.png

Final Query(LIFO)

SQL
WITH LIFO AS (
  SELECT 
    W1.purchase_date AS stock_date, 
    W1.unit_price AS unit_price,
    SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
    SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
  FROM WidgetInventory AS W1, WidgetInventory AS W2
  WHERE W1.purchase_date <= W2.purchase_date
  GROUP BY W1.purchase_date, W1.unit_price
)

SELECT
  stock_date,
  tot_cost,
  tot_qty_on_hand,
  (tot_qty_on_hand - 100),
  unit_price,
  (tot_cost - ((tot_qty_on_hand - 100) * unit_price)) AS cost
FROM 
  LIFO AS L1
WHERE 
  stock_date = (SELECT MAX(stock_date) FROM LIFO AS L2 
                WHERE tot_qty_on_hand >= 100)

write the condition to choose purchase_date(2005-08-03)
in the WHERE clause
　⇒ stock_date = (SELECT MAX(stock_date) ・・・
　⇒ get the row of 2005-08-03

▼ Output(LIFO)

stock_date tot_cost tot_qty_on_hand tot_qty_on_hand - 100 unit_price cost
2005-08-03 1390 120 20 13 1130

purchase price for 100 units is 1130 by LIFO

tot_cost
　⇒ the total purchase price on 2005-08-03
　⇒ 1390
tot_qty_on_hand
　⇒ the total inventory units on 2005-08-03
　⇒ 120 units
tot_qty_on_hand - 100
　⇒ 120 - 100 = 20
　　 20 extra units
unit_price
　⇒ unit price on 2005-08-03
　⇒ 13
cost
　⇒ tot_cost - (purchase price for 20 units)
　⇒ 1390 - (20 x 13) = 1130

　
LIFO calculation is done, next is FIFO　୧(๑›◡‹ ๑)୨

Creating Source Data①(FIFO)

SQL
SELECT W1.*, W2.*									
FROM WidgetInventory AS W1, WidgetInventory AS W2

like you have done before(LIFO)
making all combinations of
the WidgetInventory table using CROSS JOIN

edit this output and make it fit to FIFO format
　⇒ use rows that are clear numbers below image

▼ Output
re_7.png

▼ Look at the difference of W2.WidgetInventory (´◉ω◉`)
re_8.png

Creating Source Data②(FIFO)

SQL
SELECT W1.*, W2.*
FROM WidgetInventory AS W1, WidgetInventory AS W2
WHERE W1.purchase_date >= W2.purchase_date 
                    -- ↑ check operator

【memo】
compared to LIFO
the direction of the comparison operator is opposite

filter the output using two purchase_date
　⇒ W1.purchase_date >= W2.purchase_date
　⇒ W2.purchase_date is the same as
　　or earlier than W1.purchase_date
　⇒ 15 rows left

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-01
　⇒ one W2.purchase_date matches the condition

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-02
　⇒ two W2.purchase_date match the condition
　⇒ check image below

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-04
　⇒ four W2.purchase_date match the condition
　⇒ check image below

▼ Output(left side of image)
re_9.png

Edit Source Data①(FIFO)

SQL
SELECT
  W1.purchase_date AS stock_date, 
  W1.unit_price AS unit_price,
  SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
  SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
FROM WidgetInventory AS W1, WidgetInventory AS W2
WHERE W1.purchase_date >= W2.purchase_date
GROUP BY W1.purchase_date, W1.unit_price

ORDER BY W1.purchase_date

▼ Output

purchase_date unit_price tot_qty_on_hand tot_cost
2005-08-01 10 15 150.00
2005-08-02 12 40 450.00
2005-08-03 13 80 970.00
2005-08-04 12 115 1390.00
2005-08-05 10 160 1840.00

let's take a look this query

GROUP BY (purchase_date & unit_price) on the left side
then, you can calculate 『purchase price and quantity』
using SUM on the right side columns
　⇒ check the blue part of the below image

▼ preparing to SUM(purchase price and quantity)
re_10.png

Calculate past purchase price
　⇒ check below image

purchase_date：2005-08-01
　⇒ unit price：10
　⇒ 15 pieces in stock
　　purchase price：150(10 x 15)

～～～～～～～～～～～～～～～～～～～～～～
purchase_date：2005-08-03
　⇒ unit price：13
　⇒ 40 pieces in stock
　　purchase price：520(13 x 40)

　⇒ added up with 2005-08-01 and 2005-08-02
　　80 pieces in stock
　　purchase price：970

・・・ do the same way ~

▼ Calculate past purchase price
re_11.png

what you want is the purchase price for 100 units

purchase_date：2005-08-03
⇒ 80 pieces in stock

purchase_date：2005-08-04
⇒ 115 pieces in stock

the next job is ・・
taking out the row of purchase_date(2005-08-04) and minus 15

Edit Source Data②(FIFO)

SQL
WITH FIFO AS (
  SELECT 
    W1.purchase_date AS stock_date, 
    W1.unit_price AS unit_price,
    SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
    SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
  FROM WidgetInventory AS W1, WidgetInventory AS W2
  WHERE W1.purchase_date >= W2.purchase_date
  GROUP BY W1.purchase_date, W1.unit_price
)

SELECT 
 L1.*,
 (SELECT MIN(stock_date) FROM FIFO AS L2 
  WHERE tot_qty_on_hand >= 100) AS min_date
FROM FIFO AS L1

▼ Output (add：min_date)

purchase_date unit_price tot_qty_on_hand tot_cost min_date
2005-08-01 10 15 150.00 2005-08-04
2005-08-02 12 40 450.00 2005-08-04
2005-08-03 13 80 970.00 2005-08-04
2005-08-04 12 115 1390.00 2005-08-04
2005-08-05 10 160 1840.00 2005-08-04

Rewrite 『Edit Source Data①(FIFO)』
　⇒ write using CTE, its name is FIFO

min_date column is added in the SELECT statement for checking
　⇒ stock_date when total inventory is over 100 units
　⇒ there are two days
　　 2005-08-04 and 2005-08-05
　⇒ select the latest date
　　 MIN(stock_date)
　　 2005-08-04

you used MAX() function for FIFO calculation
but here
you use MIN() function instead

▼ taking out purchase_date(2005-08-04)
re_12.png

the last jos is ・・・

by subtracting 15 units
from the total inventory held on 2005-08-04
then, you can calculate the purchase price for 100 units

▼ purchase price for 100 units
re_13.png

Final Query(FIFO)

SQL
WITH FIFO AS (
  SELECT 
    W1.purchase_date AS stock_date, 
    W1.unit_price AS unit_price,
    SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
    SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
  FROM WidgetInventory AS W1, WidgetInventory AS W2
  WHERE W1.purchase_date >= W2.purchase_date
  GROUP BY W1.purchase_date, W1.unit_price
)

SELECT
  stock_date,
  tot_cost,
  tot_qty_on_hand,
  (tot_qty_on_hand - 100),
  unit_price,
  (tot_cost - ((tot_qty_on_hand - 100) * unit_price)) AS cost
FROM 
  FIFO AS L1
WHERE 
  stock_date = (SELECT MIN(stock_date) FROM FIFO AS L2 
                WHERE tot_qty_on_hand >= 100)

write the condition to choose purchase_date(2005-08-04)
in the WHERE clause
　⇒ stock_date = (SELECT MIN(stock_date) ・・・
　⇒ get the row of 2005-08-04

▼ Output(FIFO)

stock_date tot_cost tot_qty_on_hand tot_qty_on_hand - 100 unit_price cost
2005-08-04 1390 115 15 12 1210

purchase price for 100 units is 1210 by FIFO

tot_cost
　⇒ the total purchase price on 2005-08-04
　⇒ 1390
tot_qty_on_hand
　⇒ the total inventory units on 2005-08-04
　⇒ 115 units
tot_qty_on_hand - 100
　⇒ 115 - 100 = 15
　　 15 extra units
unit_price
　⇒ unit price on 2005-08-03
　⇒ 13
cost
　⇒ tot_cost - (purchase price for 20 units)
　⇒ 1390 - (15 x 13) = 1210

that's all

personally ・・

The technique of this puzzle is
to create a combination of purchase dates
and use the "MAX & MIN" functions
to choose a specific day

References

Joe Celko's SQL Puzzles and Answers

1
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?