SQL for FIFO Inventory Query ?

[Steve Guidos]

I have a table containing "layers" of inventory purchases set up as such:

PurchaseDate Qty Cost
11/07/2003 600 6.35
11/10/2003 100 6.15
11/12/2003 400 6.00
11/15/2003 500 6.25

I want to create a stored procedure query that returns a resultset of layers
for a given current inventory level. For example, 'exec
spSelectInventoryLayers 700' would return the records:

PurchaseDate Qty Cost
11/15/2003 500 6.25
11/12/2003 200 6.00

I have written the sp using a cursor but it seems very inefficient,
especially as my table grow larger. There are several threads on FIFO and
LIFO inventory but either they seem to assume that there is a 'running
total' in the inventory table or else I am missing their point entirely.

I would even be happy to have a query that returns the "extra" units in the
bottom layer and I would then adjust the "bottom" layer in my client code,
e.g. a query returning:

PurchaseDate Qty Cost
11/15/2003 500 6.25
11/12/2003 400 6.00

Given the above table structure, is it possible to create a query for what I
need? Even an sp with a temp table would seem to be preferable to using
cursors, correct?

Steve Guidos

[Jacco Schalkwijk]

CREATE PROC spSelectInventoryLayers @Qty INT
AS
SET NOCOUNT ON
SELECT PurchaseDate, Qty, Cost
FROM some_table s1
WHERE (SELECT ISNULL(SUM(qty), 0) FROM some_table s2
WHERE s2.PurchaseDate > s1.PurchaseDate) < @Qty

--
Jacco Schalkwijk
SQL Server MVP

"Steve Guidos" <sgu...@trackingtek.com> wrote in message
news:b_Kub.20437$qG....@fe04.private.usenetserver.com...

[Eric Sabine]

Steve, I think your first example is LIFO. But I think you need to add
another column to your table such as "USED" which has a check constraint
limiting it to between 0 and the QTY column. I assume here we're looking at
the same inventory item, but let me elaborate on your table.

pkid date part qty cost used
1 11/7/03 abc 600 6.35 0
2 11/10/03 abc 100 6.15 0
3 11/12/03 abc 400 6.00 0
4 11/15/03 def 50 2.25 45
5 11/15/03 abc 500 6.25 0

Now a shipment goes out for 700 pieces you would need to select the first
(and only) row that meets your criteria. In FIFO order by the date ADC, in
LIFO, order by the date DESC. The reason you get one row at a time is you
probably need to perform a ledger transaction. So in LIFO, we ship 500
pieces, create a journal entry for cost of goods sold as 500 * 6.25. Then
we update the USED column to 500. Now we have 200 more pieces to ship. So
we start over. We find the next record, PKID = 3 for 400 pieces. We create
our JE for 200 * 6.00 and we update USED to be 200.

This could be accomplished in your front end code, middle layer, with a team
of stored procedures, and even, _gasp_ a cursor. I don't really recommend
the cursor though.

There is a major problem with this solution though. What happens at month
end when Joe receiving guy calls you and says "Hey, that receipt on 11/15/03
for part ABC was bogus. Can you delete it?" You already used it. What do
you do? You could move the USED value to another record which has the
appropriate quantity, but you still have that incorrect journal entry that
you will have to fix. And what if the record you move the 500 pieces to
doesn't exist, but you can use 4 other receipts to total up to the 500. It
sure can get complex. You could go with a 2 table solution. One for
RECEIPTS, as you have and one for SHIPMENTS. But this solution requires a
reconcilliation period to happen occasionally such as month end. At this
period, you would basically process every shipment and create the journal
entries and reconcile the outgoing with the incoming. But alas, there is
another problem. Negative inventory. If you record incoming and outgoing
transactions, at the month-end, you might find that inventory went negative.
Obviously that can't happen but with missing records of receipts, it could
at month-end. Then you have to go back with your shipping/receiving people
and discover what went wrong. With the 2 table solution, you still have to
handle RMA receipts and inventory stock adjustments, such as "hey I found 52
pieces" or "hey I just counted and there are 200 pieces less than the
database thinks we have." None if this is really fun.

hope I could help with some of the other problems you might encounter.

Eric

"Steve Guidos" <sgu...@trackingtek.com> wrote in message
news:b_Kub.20437$qG....@fe04.private.usenetserver.com...

[David Portas]

Contrary to your subject line, your example is LIFO (seems slightly unusual
for a stock inventory).

Here's a version that adjusts the quantity on the first row to make a
correct total in the result set. This assumes Purchasedate is unique.

CREATE TABLE Stock (purchasedate DATETIME PRIMARY KEY, qty INTEGER NOT NULL
CHECK (qty>=0), cost NUMERIC(6,2) NOT NULL)

INSERT INTO Stock VALUES ('20031107', 600, 6.35)
INSERT INTO Stock VALUES ('20031110', 100, 6.15)
INSERT INTO Stock VALUES ('20031112', 400, 6.00)
INSERT INTO Stock VALUES ('20031115', 500, 6.25)

DECLARE @qty INTEGER
SET @qty = 700

SELECT S.purchasedate,
CASE S.purchasedate WHEN L.purchasedate THEN L.qty ELSE S.qty END AS qty,
S.cost
FROM Stock AS S
JOIN
(SELECT S1.purchasedate, @qty-COALESCE(SUM(S2.qty),0)
FROM Stock AS S1
LEFT JOIN Stock AS S2
ON S1.purchasedate < S2.purchasedate
GROUP BY S1.purchasedate, S1.qty, S1.cost
HAVING @qty > COALESCE(SUM(S2.qty),0)
AND @qty <= COALESCE(SUM(S2.qty),0)+S1.qty)
AS L (purchasedate,qty)
ON S.purchasedate >= L.purchasedate

Note that an empty result is returned if @qty exceeds the total quantity
available, which is a useful way to detect that exception.

--
David Portas
------------
Please reply only to the newsgroup
--

[Steve Kass]

Steve,

Here's a solution that might help you. If you meant FIFO, you need to
change '19000101' to '99991231', remove DESC, and change the direction of
all the inequalities between datetime values.

In the past I've found this to be a good compromise between pure set-based
queries and cursors.

CREATE TABLE Stock (purchasedate DATETIME PRIMARY KEY, qty INTEGER NOT NULL
CHECK (qty>=0), cost NUMERIC(6,2) NOT NULL)

INSERT INTO Stock VALUES ('20031107', 600, 6.35)
INSERT INTO Stock VALUES ('20031110', 100, 6.15)
INSERT INTO Stock VALUES ('20031112', 400, 6.00)
INSERT INTO Stock VALUES ('20031115', 500, 6.25)

go

create view stockV as
select purchasedate, qty, cost from stock
union all
select '19000101',99999999,0

go
create procedure sell_some (
@to_sell int OUTPUT,
@orig_cost int OUTPUT
) as
declare @cantfill datetime
declare @usedup int
select
@cantfill = cantfill,
@usedup = coalesce(usedup,0)
from (
select TOP 1
T1.purchasedate as cantfill,
SUM(T2.qty) as usedup,
SUM(T2.cost) as orig_cost
from stockV T1 join stockV T2
on T2.purchasedate >= T1.purchasedate
group by T1.purchasedate
having sum(T2.qty) > @to_sell
order by T1.purchasedate desc
) X

set @to_sell = @to_sell - @usedup + (
select qty from stockV where purchasedate = @cantfill
)

set @orig_cost = (
select sum(case when purchasedate = @cantfill then @to_sell else qty
end *
cost)
from stockV
where purchasedate >= @cantfill
)

delete stock
where purchasedate > @cantfill

update stock
set qty = qty - @to_sell
where purchasedate = @cantfill
go

select * from stock

declare @q int
declare @c int
set @q = 100

select @q as must_sell
exec sell_some @q, @c output
select @c as value_just_sold
select * from stock

set @q = 300
select @q as must_sell
exec sell_some @q, @c output
select @c as value_just_sold
select * from stock

set @q = 400
select @q as must_sell
exec sell_some @q, @c output
select @c as value_just_sold
select * from stock

go

drop table stock
drop procedure sell_some
drop view stockV
go

SK

[Steve Guidos]

"Eric Sabine" <mop...@hyottmail.com> wrote

> Steve, I think your first example is LIFO. But I think you need to add
> another column to your table such as "USED" which has a check constraint
> limiting it to between 0 and the QTY column.

A couple of other people suggested the same thiing, but I'm pretty sure it's
FIFO. Remember that in my example I am looking for what is in the CURRENT
INVENTORY - I am not looking to find my cost for a given order being SOLD
(taken out of inventory). Under FIFO the layers / items remaining in my
inventory will always be my most recent purchases / layers, because the
earlier layers (first in) will have been sold first (out) before my more
recent layers. In my example, my FIFO inventory would be the 11/15/2003 and
11/12/2003 as I originally described.

Another thing that may be semi-unique to my problem is that I cannot
specifically identify the goods in my inventory - the actual product I am
looking at is kerosene, each purchase / load gets dumped into the same tank
and then sold during the day. That is why I cannot use a "used" column like
you and others have suggested, there is no way (and no reason) to identify
which kerosene is being sold, it's just one gallon of kerosene from the same
big tank.

MVP Jacco wrote out a simple query that seems like it will work perfectly
for what I need, and I appreciate the help from you and the others who
resonded.

Steve Guidos

[Steve Guidos]

Jacco:

THANKS! This is exactly the statement I was looking for. It does not 'chop
off' the bottom layer and therefore will usually return a greater quantity
than @Qty, but as I said I can take care of that in my client-side code.

What confuses me now is, how does the statement "know" to sort the data
layers by date? There is no ORDER BY clause yet the result is always sorted
by PurchaseDate DESC, at least for my small test table. Why does this
happen?

Many thanks once again.

Steve Guidos

"Jacco Schalkwijk" <NOSPAM...@eurostop.co.uk> wrote in message
news:ufEIX8qr...@tk2msftngp13.phx.gbl...

[Eric Sabine]

OK, you are performing an inventory valuation hence you want to see what is
left in inventory. I did not get that from your original post. Perhaps my
fault. You can still use a USED column though. I don't see how it would be
a problem other than to strict normalization rules I suppose. There is no
difference between liquid, parts, or T-Shirts. It's just inventory. FIFO,
LIFO, (FISH), and Weighted Average are just bean-counting methods. No one
is right or wrong but it requires IRS approval to switch. I digress. But
you still can run into the problem of an incorrect receipt, such as one
being ficticious. Glad Jacco could get you set.

Eric

"Steve Guidos" <sgu...@trackingtek.com> wrote in message

news:pfRub.7464$mp4....@fe01-2.private.usenetserver.com...

[David Portas]

The only way to guarantee the order of rows in a result set is to use ORDER
BY. Any given query without ORDER BY may appear to return rows in a
consistent order each time you run it but this is dependent on the chosen
execution plan for the query. The ordering could change unexpectedly if the
base data changes or new indexes are created.

Make sure you add ORDER BY if the ordering is importing to you. This applies
to my solution as well as Jacco's - in common with many others who post
query solutions I generally leave out ORDER BY unless it is specifically
asked for.

[Jacco Schalkwijk]

You can get the exact results you want with:

SELECT
PurchaseDate,
CASE WHEN (SELECT SUM(qty) FROM some_table s2
WHERE s2.PurchaseDate >= s1.PurchaseDate) > @Qty
THEN @Qty - (SELECT ISNULL(SUM(qty), 0) FROM some_table s2
WHERE s2.PurchaseDate > s1.PurchaseDate)
ELSE Qty
END AS Qty,

Cost
FROM some_table s1
WHERE (SELECT ISNULL(SUM(qty), 0) FROM some_table s2
WHERE s2.PurchaseDate > s1.PurchaseDate) < @Qty

--
Jacco Schalkwijk
SQL Server MVP

"Steve Guidos" <sgu...@trackingtek.com> wrote in message

news:FhRub.7483$mp4...@fe01-2.private.usenetserver.com...

[Jacco Schalkwijk]

> Glad Jacco could get you set.

That's because I got no clue (ok, not much of a clue) about LIFO, FIFO and
the other bean counting methods and was only looking at the SQL side of the
problem ;-)

--
Jacco Schalkwijk
SQL Server MVP

"Eric Sabine" <mop...@hyottmail.com> wrote in message
news:3fbbe906$0$43850$39ce...@news.twtelecom.net...