Fee Fi Fo SQL

A solution that has eluded me for months now is one where First In - First Out (FIFO) inventory costing can be represented in a single SQL statement. I knew it could be done with a pipelined function, but for my own personal satisfaction I had to see it done with SQL Analytics.

It wasn't until I looked at the problem from the bottom up did the solution really present itself. For example, if you know how much inventory you have on hand, you simply need to spread it over your most recent incoming shipments.

Here's an emp example using salary and hiredate. Vary the on hand amount when prompted and you'll see it spreads out the on-hand amount on a first in - first out basis. Success!


SELECT
   empno,hiredate,sal,
   LEAST(sal, remain_bal) remaining_qty
FROM
 ( SELECT
      empno,hiredate,sal, on_hand,
      NVL(LAG(run_ttl) OVER (ORDER by hiredate DESC,
                                      empno DESC),on_hand) as remain_bal
   FROM
    ( SELECT empno,hiredate,sal, on_hand,
             on_hand - SUM(sal) OVER (ORDER BY hiredate DESC,
                                               empno DESC) AS run_ttl
      FROM
       ( SELECT empno,hiredate,sal, 
                :on_hand AS on_hand
           FROM scott.emp
          ORDER BY 2 DESC,1 DESC )
  ORDER BY 2 DESC,1 DESC ))
WHERE LEAST(sal, remain_bal) > 0


Here's some sample output...

On_Hand: 1800
empno hiredate  sal     remaining_qty
7876  5/23/1987 1100.01    1100.01
7788  4/19/1987 3000        699.99

On_Hand: 5500
empno hiredate    sal   remaining_qty
7876  5/23/1987 1100.01    1100.01
7788  4/19/1987 3000       3000.00
7934  1/23/1982 1300       1300.00
7902  12/3/1981 3000         99.99

There you have it. Of course if your needs are Last In - First Out (LIFO) you'll simply need to reverse the order of the incoming shipments.

Our journey continues...