FIFO Implementation in Inventory using SQL

Question

This is basically an inventory project which tracks the "Stock In" and "Stock Out" of items through Purchase and sales respectively.

The inventory system follows FIFO Method (the items which are first purchased are always sold first). For example:

If we purchased Item A in months January, February and March When a customer comes we give away items purchased during January only when the January items are over we starts giving away February items and so on

So I have to show here the total stock in my hand and the split up so that I can see the total cost incurred.

Actual table data:

Actual table data

The result set I need to obtain:

What the result set is meant to be

My client insists that I should not use Cursor, so is there any other way of doing so?

A cursor solution will be by far the most efficient solution then. What's wrong with the cursors, anyway? — dean, Apr 08 '14 at 12:09
@dean - a lot is wrong with the cursors - http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them Sure there are situations where they could/should be used, but this hardly one of those — Nenad Zivkovic, Apr 08 '14 at 12:15
With windowing functions as they were in 2008, cursor will outperform any set-based solution for this particular problem (somehow I guess that real world data is not so clean). Feel free to prove me wrong :) — dean, Apr 08 '14 at 12:18
A recursive CTE could do the trick: [Recursive Queries](http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx). BTW what is PK of this table? — Edwin Stoteler, Apr 08 '14 at 12:21
@Harish Can you explain a bit detailed your requirements? How do you get two rows with `Item A` in `WH1` with same price in results? — Nenad Zivkovic, Apr 08 '14 at 12:40
Say it like i purchased item A on Monday as price was 10.00, then i again purchased on tuesday for same price, its just purchase and sales, some times price goes up some time not, i need to compute the inventory value thats it!! — Harish, Apr 08 '14 at 12:43
@Harish - How is the table sorted? You mention months, but I don't see them there. If my understanding is correct, then the sort order is pretty important.. — StevieG, Apr 08 '14 at 12:43
sorry there is a date field and i have sorted it using date ... — Harish, Apr 08 '14 at 12:44
Also, just to be clear, if I understand the requirement correctly, the `Price` in row 1 of your results should be `300.00` not `500.00` ? — StevieG, Apr 08 '14 at 12:48
wow no cursor, i want to see the answer... in my opinion this case need a loop. i have a FIFO query but it use CURSOR or use loop in the code. i really want to see this question answered...thanks — cyan, Apr 08 '14 at 12:48

Serpiton · Accepted Answer · 2014-04-09T09:19:27.860

As some comment already said a CTE can solve this

with cte as (
select item, wh, stock_in, stock_out, price, value
     , row_number() over (partition by item, wh order by item, wh) as rank
from   myTable)
select a.item, a.wh
     , a.stock_in - coalesce(b.stock_out, 0) stock
     , a.price
     , a.value - coalesce(b.value, 0) value
from cte a
     left join cte b on a.item = b.item and a.wh = b.wh and a.rank = b.rank - 1
where a.stock_in - coalesce(b.stock_out, 0) > 0

If the second "Item B" has the wrong price (the IN price is 25, the OUT is 35).
SQL 2008 fiddle

Just for fun, with sql server 2012 and the introduction of the LEAD and LAG function the same thing is possible in a somewhat easier way

with cte as (
select item, wh, stock_in
     , coalesce(LEAD(stock_out) 
                OVER (partition by item, wh order by item, wh), 0) stock_out
     , price, value
     , coalesce(LEAD(value) 
                OVER (partition by  item, wh order by item, wh), 0) value_out
from   myTable)
select item
     , wh
     , (stock_in - stock_out) stock
     , price
     , (value - value_out) value
from   cte
where  (stock_in - stock_out) > 0

SQL2012 fiddle

Update
ATTENTION -> To use the two query before this point the data need to be in the correct order.

To have the details with more then one row per day you need something reliable to order the row with the same date, like a date column with time, an autoincremental ID or something down the same line, and it's not possible to use the query already written because they are based on the position of the data.

A better idea is to split the data in IN and OUT, order it by item, wh and data, and apply a rank on both data, like this:

SELECT d_in.item
     , d_in.wh
     , d_in.stock_in - coalesce(d_out.stock_out, 0) stock
     , d_in.price
     , d_in.value - coalesce(d_out.value, 0) value
FROM   (SELECT item, wh, stock_in, price, value
             , rank = row_number() OVER 
               (PARTITION BY item, wh ORDER BY item, wh, date)
        FROM   myTable
        WHERE  stock_out = 0) d_in
       LEFT JOIN
       (SELECT item, wh, stock_out, price, value
             , rank = row_number() OVER 
               (PARTITION BY item, wh ORDER BY item, wh, date)
        FROM   myTable
        WHERE  stock_in = 0) d_out
       ON d_in.item = d_out.item AND d_in.wh = d_out.wh 
      AND d_in.rank = d_out.rank
WHERE d_in.stock_in - coalesce(d_out.stock_out, 0) > 0

SQLFiddle

But this query is NOT completely reliable, the order of data in the same order group is not stable.

I haven't change the query to recalculate the price if the IN.price is different from the OUT.price

hmm for the inventory value probably like this (a.stock_in - coalesce(b.stock_out, 0)) * a.price) as 'Value' — cyan, Apr 08 '14 at 13:55
@cyan the Value is money so (Value_IN - Value_OUT) is probably correct, is the price that need some calc, like (Value_IN - Value_OUT) / (stock_in - stock_out) — Serpiton, Apr 08 '14 at 14:07
I wish I was clever enough to wrap my mind around TSQL solutions like this. +1 — Crono, Apr 08 '14 at 14:35
@Serpiton We need the details we can't group the data by details — Harish, Apr 09 '14 at 08:43

Crono · Answer 2 · 2014-04-08T13:17:56.333

1

If cursors aren't an option, a SQLCLR stored procedure might be. This way you could obtain the raw data into .net objects, manipulate / sort it using c# or vb.net and set the resulting data as the procedure's output. Not only this will give you what you want, it may even turn up being much easier than trying to do the same in pure T-SQL, depending on your programming background.

edited Apr 08 '14 at 13:17

answered Apr 08 '14 at 13:02

Crono

10,211
6
43
75

I want to note that using SQLCLR opens up a security flaw for those who are considering it. – BilliD Oct 27 '16 at 19:18
@BilliD There are three levels of security an SQLCLR assembly can be set to. On the SAFE level, which IMHO is sufficient for the problem at hand, there shouldn't be any security issues. – Crono Oct 27 '16 at 19:26

FIFO Implementation in Inventory using SQL

2 Answers2

Linked