Stock take FiFo conundrum

Question

Stock take FiFo conundrum

Dom Horton

SSCrazy

Points: 2202
More actions
October 16, 2017 at 9:06 am

#339897

Hello,
I'd very much appreciate some advice/help with a stock take problem.
I've had a look at https://ask.sqlservercentral.com/questions/1961/the-fifo-stock-inventory-sql-problem.html
but my scenario differs somewhat.
I have a stocktake table (where someone has physically counted the products in a warehouse), which lists products located in 'bins', their stock take quantity and an expected system quantity (also included the difference between stock count & expected, & a running total for each product)
Note, the same product can appear in different bins:
--===== If the test table already exists, drop it      IF OBJECT_ID('TempDB..#myStockTake','U') IS NOT NULL          DROP TABLE #myStockTake
--===== Create the test table with
CREATE TABLE #myStockTake
        (
        bin varchar(5),
        seq INT,
  product varchar(20),
  qty int,
  expectedqty int,
  stockdiff int,
  runtot int)

INSERT INTO #myStockTake
(bin,seq,product,qty,expectedqty,stockdiff,runtot)
SELECT 'A01','1','180022','11','21','-10','-10' UNION ALL
SELECT 'A01A','2','180022','0','3','-3','-13' UNION ALL
SELECT 'A01','1','180023','5','6','-1','-1' UNION ALL
SELECT 'A01','1','180026','15','13','2','2' UNION ALL
SELECT 'A01A','2','180026','0','1','-1','1' UNION ALL
SELECT 'A01','1','180027','33','24','9','9' UNION ALL
SELECT 'A01','1','180029','6','1','5','5' UNION ALL
SELECT 'A01','1','180031','90','93','-3','-3' UNION ALL
SELECT 'A01A','2','180031','0','2','-2','-5' UNION ALL
SELECT 'A01','1','180033','69','86','-17','-17' UNION ALL
SELECT 'A01A','2','180033','0','3','-3','-20'
select * from #myStockTake
I also have a stock fifo table, which lists stock additions to a warehouse at a certain unit price using First In First Out.
--===== If the test table already exists, drop it      IF OBJECT_ID('TempDB..#myStockFiFo','U') IS NOT NULL          DROP TABLE #myStockFiFo
--===== Create the test table with
CREATE TABLE #myStockFiFo
        (
  product varchar(20),
  seq INT,
  qty int,
  UnitValueGBP float,
  runtot int)

INSERT INTO #myStockFiFo
(product,seq,qty,UnitValueGBP,runtot)
SELECT '180022','1','11','42.11','11' UNION ALL
SELECT '180022','2','10','42.11','21' UNION ALL
SELECT '180022','3','3','42.11','24' UNION ALL
SELECT '180022','4','24','42.11','48' UNION ALL
SELECT '180022','5','29','42.11','77' UNION ALL
SELECT '180022','6','4','42.11','81' UNION ALL
SELECT '180023','1','4','42.11','4' UNION ALL
SELECT '180026','1','38','42.11','38' UNION ALL
SELECT '180026','2','8','42.11','46' UNION ALL
SELECT '180027','1','1','42.11','1' UNION ALL
SELECT '180027','2','3','42.11','4' UNION ALL
SELECT '180027','3','5','42.11','9' UNION ALL
SELECT '180029','1','1','42.11','1' UNION ALL
SELECT '180031','1','2','35.125','2' UNION ALL
SELECT '180031','2','17','35.11','19' UNION ALL
SELECT '180031','3','11','35.7145','30' UNION ALL
SELECT '180031','4','3','36.51','33' UNION ALL
SELECT '180031','5','5','36.51','38' UNION ALL
SELECT '180031','6','3','36.51','41' UNION ALL
SELECT '180031','7','10','36.51','51' UNION ALL
SELECT '180031','8','6','36.51','57' UNION ALL
SELECT '180031','9','6','36.51','63' UNION ALL
SELECT '180031','10','15','36.51','78' UNION ALL
SELECT '180031','11','1','37.34','79' UNION ALL
SELECT '180031','12','10','37.34','89' UNION ALL
SELECT '180031','13','8','36.51','97' UNION ALL
SELECT '180033','1','11','35.11','11' UNION ALL
SELECT '180033','2','4','36.51','15' UNION ALL
SELECT '180033','3','8','36.51','23' UNION ALL
SELECT '180033','4','3','36.51','26' UNION ALL
SELECT '180033','5','10','36.51','36' UNION ALL
SELECT '180033','6','6','36.51','42' UNION ALL
SELECT '180033','7','4','36.51','46' UNION ALL
SELECT '180033','8','19','36.51','65' UNION ALL
SELECT '180033','9','2','38.01','67' UNION ALL
SELECT '180033','10','9','36.51','76' UNION ALL
SELECT '180033','11','11','36.51','87' UNION ALL
SELECT '180033','12','5','38.01','92'
Select * from #myStockFiFo
What I need to do is reconcile the stock held in fifo with the data gained from the stock take.
So bin A01 for product 18002 contains 11. Whereas it should have 21. The difference being -10.
I then need to take the first fifo entry for this product and apply the UnitValueGBP for this difference in qty as a Loss. In this case, the fifo entry has 11 items so this fifo entry will 'cover' the -10 difference with 1 to spare.
This 1 product at that UnitValueGBP will then be carried over to the next bin, in this case A01A where we have a difference of -3 leaving us with -2. We'd then move onto the 2nd fifo entry and apply this UnitValueGBP to the -2.
We'd continue in this manner until all differences are accounted for in the various bins, using first in & first out logic (in most cases the UnitValueGBP is the same but we need to assume that it may differ between fifo entries).
For row 4, Product 180026 in bin A01, we have a gain of 2, so would apply the UnitValueGBP from row 8 in fifo as a Gain. We still use this fifo entry for the 180026 held in bin A01A. This time we're down by 1, so we'd apply a Loss.
My thinking on how to solve this would be via a cursor, but not having much experience with these I'm struggling.
This below DOES NOT WORK, but it's where I've got to with using cursors (just to show that I have had a go at trying to solve this!):
DECLARE @Result Table(product nvarchar(50), bin nvarchar(10), qty int, expectedqty int, stockdiff int, runtot int,runqty int, value float);
DECLARE
@product nvarchar(50), @prevproduct nvarchar(50), @bin nvarchar(10), @qty int, @runqty int, @value float;
DECLARE C Cursor fast_forward for
SELECT
product
,qty
,UnitValueGBP
from #myStockFiFo
OPEN C
FETCH NEXT FROM C INTO @product, @qty, @value;
SELECT @prevproduct = @product, @runqty=0;
while @@FETCH_STATUS = 0
BEGIN
IF @product <> @prevproduct
SELECT @prevproduct = @product, @runqty = 0;
SET @runqty = @runqty + @qty
INSERT INTO @Result (product, bin , qty,expectedqty, stockdiff ,runtot, runqty , value )
SELECT
product,
bin,
qty,
expectedqty,
stockdiff,
runtot,
@runqty,
@value
FROM #myStockTake
where product = @product
FETCH NEXT FROM C INTO @product, @qty, @value;
END
CLOSE C;
DEALLOCATE C;
SELECT * FROM @Result
Any help greatly appreciated and thanks in advance.
Dom

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Dom, can you knock up a table of expected results please? Cheers.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dom Horton SSCrazy Points: 2202 More actions · Answer 2

SELECT 'A01' as bin,'1' as seq,'180022' as product,'11' as qty,'21' as expectedqty,'-10' as stockdiff,-10*42.11 as GainLoss UNION ALL SELECT 'A01A','2','180022','0','3','-3',-1*42.11+-2*42.11 UNION ALL SELECT 'A01','1','180023','5','6','-1',-1*42.11 UNION ALL SELECT 'A01','1','180026','15','13','2',2*42.11 UNION ALL SELECT 'A01A','2','180026','0','1','-1',-1*42.11 UNION ALL SELECT 'A01','1','180027','33','24','9',1*42.11+3*42.11+5*42.11 UNION ALL SELECT 'A01','1','180029','6','1','5', 5*42.11 UNION ALL --possible error (not enough entries in fifo so will require further logic) SELECT 'A01','1','180031','90','93','-3',-2*35.125+-1*35.11 UNION ALL SELECT 'A01A','2','180031','0','2','-2',-2*35.11 UNION ALL SELECT 'A01','1','180033','69','86','-17', -4*36.51+-8*36.51+-3*36.51+-2*36.51 UNION ALL SELECT 'A01A','2','180033','0','3','-3', -3*36.51

This should show desired output. The GainLoss column shows a calculation of stockdiff against fifo records. So the first entry, -10 multiplied by the 10 available records in the first fifo entry for that product.
The second entry shows the -1 carried over at that rate and -2 at the next fifo rate (unfortunately most of the rates are the same which makes reading it a bit difficult).
If we look at Product 180031 in bin A01, the quantity is -3. We'd then apply the first fifo record which would be -2 * 35.125, leaving us with -1 to be calculated using the next fifo record, therefore -1 * 35.11

BTW, I may have to go back to 'stakeholder' and confirm some of the business logic (e.g. what happens when we don't have enough fifo quantities). But if I can get to a point where we have the bare bones of a cursor (or set-based) solution up and running that'll be great.

many thanks

Dom Horton SSCrazy Points: 2202 More actions · Answer 3

Hopefully, this is more useful test data:
--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#myStockTake','U') IS NOT NULL DROP TABLE #myStockTake

--===== Create the test table with
CREATE TABLE #myStockTake
        (
        bin varchar(5),
        seq INT,
product varchar(20),
qty int,
expectedqty int,
stockdiff int,
runtot int)

INSERT INTO #myStockTake
(bin,seq,product,qty,expectedqty,stockdiff,runtot)
SELECT 'AA01A','1','FS-UP2100809006','15','20','-5','-5' UNION ALL
SELECT 'BB01A','2','FS-UP2100809006','15','10','5','0' UNION ALL
SELECT 'CC01A','3','FS-UP2100809006','0','20','-20','-20' UNION ALL
SELECT 'DD01A','4','FS-UP2100809006','30','10','20','0' UNION ALL
SELECT 'FF01B','5','FS-UP2100809006','20','50','-30','-30' UNION ALL
SELECT 'GG01A','6','FS-UP2100809006','30','0','30','0'
select * from #myStockTake

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#myStockFiFo','U') IS NOT NULL
DROP TABLE #myStockFiFo

--===== Create the test table with
CREATE TABLE #myStockFiFo
(
product varchar(20),
seq INT,
qty int,
UnitValueGBP float,
runtot int)

INSERT INTO #myStockFiFo
(product,seq,qty,UnitValueGBP,runtot)
SELECT 'FS-UP2100809006','1','10','5.8','10' UNION ALL
SELECT 'FS-UP2100809006','2','10','60','20' UNION ALL
SELECT 'FS-UP2100809006','3','10','1','30' UNION ALL
SELECT 'FS-UP2100809006','4','30','30','60' UNION ALL
SELECT 'FS-UP2100809006','5','50','0','110'
select * from #myStockFiFo

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Hi Dom, try this, first in English.
In the Stocktake table;
For each product, ordered by seq;
Determine a RangeStart and RangeEnd.
For seq = 1, RangeStart = 1 and RangeEnd = ABS(stockdiff) = 10
For seq = 2, RangeStart = (RangeEnd+1 from seq = 1) = 11, RangeEnd = ABS(stockdiff) + (RangeEnd from seq = 1) = 13

In the StockFiFo table, multiply each row out by the qty, then rownumber each row by seq, partitioning by product. So if the qty is 3, create two more rows exactly the same and number them 1,2,3.

Now, for any row in the Stocktake table, collect rows from StockFiFo matching on Product, where the StockFiFo rownumber is between Stocktake RangeStart and RangeEnd. This means you will pick up the correct unit quantity from StockFiFo, in the correct order.
Finally, sum the UnitValueGBP and apply the correct sign

Here’s a query which shows how it works:

;WITH Calculator AS (
 SELECT bin, seq, product, qty, expectedqty, stockdiff, 
  RE = SUM(ABS(stockdiff)) OVER(PARTITION BY product ORDER BY seq) 
 FROM #myStockTake
),
RangedData AS (
 SELECT bin, seq, product, qty, expectedqty, stockdiff, 
  RS = 1+LAG(RE,1,0) OVER(PARTITION BY product ORDER BY seq), RE
 FROM Calculator
)
SELECT rd.*, '#' '#', x.*
FROM RangedData rd
CROSS APPLY (
 SELECT *
 FROM (
  SELECT  
   product, seq, qty, UnitValueGBP, rn = ROW_NUMBER() OVER(ORDER BY seq)
  FROM #myStockFiFo f
  CROSS APPLY ( -- caters for qty up to 100, expand if necessary
   SELECT TOP(f.qty) n = 0 
   FROM 
    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n)
  ) t
  WHERE f.product = rd.product
 ) q 
 WHERE q.rn BETWEEN rd.RS AND rd.RE
) x
ORDER BY rd.product, rd.seq

Here’s a query which works:

;WITH Calculator AS ( SELECT bin, seq, product, qty, expectedqty, stockdiff, RE = SUM(ABS(stockdiff)) OVER(PARTITION BY product ORDER BY seq) FROM #myStockTake ), RangedData AS ( SELECT bin, seq, product, qty, expectedqty, stockdiff, RS = 1+LAG(RE,1,0) OVER(PARTITION BY product ORDER BY seq), RE FROM Calculator ) SELECT rd.bin, rd.seq, rd.product, rd.qty, rd.expectedqty, rd.stockdiff, val = CASE WHEN rd.stockdiff < 0 then 0-x.Val ELSE x.Val END FROM RangedData rd CROSS APPLY ( SELECT Val = SUM(UnitValueGBP) FROM ( SELECT product, seq, qty, UnitValueGBP, rn = ROW_NUMBER() OVER(ORDER BY seq) FROM #myStockFiFo f CROSS APPLY ( SELECT TOP(f.qty) n = 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n), (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n) ) t WHERE f.product = rd.product ) q WHERE q.rn BETWEEN rd.RS AND rd.RE ) x ORDER BY rd.product, rd.seq

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dom Horton SSCrazy Points: 2202 More actions · Answer 5

Hi Chris,
Many thanks for the reply. Not as yet had chance to test your code as I've been dealing with another client. Hopefully get back on with this in the next day or two. Will keep you posted.
thanks, Dom

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Dom Horton - Wednesday, October 18, 2017 5:16 AM
Hi Chris,
Many thanks for the reply. Not as yet had chance to test your code as I've been dealing with another client. Hopefully get back on with this in the next day or two. Will keep you posted.
thanks, Dom

Sure thing Dom. Holler if you need any explanation.
FYI there are a couple of optimisations to this code, I'll post 'em up if I get time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dom Horton SSCrazy Points: 2202 More actions · Answer 7

Hi Chris,

Finally able to get back on with this!
Much appreciate your earlier input but unfortunately there's been a request to change the fifo logic somewhat and is probably best shown via an example (please note slight rename of cols, startqty & newqty):

--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#myStockFiFo','U') IS NOT NULL DROP TABLE #myStockFiFo

--===== Create the test table with
CREATE TABLE #myStockFiFo
(
product varchar(20),
seq INT,
startqty int,
UnitValueGBP float,
runtot int)

INSERT INTO #myStockFiFo
(product,seq,startqty,UnitValueGBP,runtot)
SELECT 'BPD-V6','1','100','140','100' UNION ALL
SELECT 'BPD-V6','2','100','50','200' UNION ALL
SELECT 'BPD-V6','3','100','250','300' UNION ALL
SELECT 'BPD-V6','4','100','3','400' UNION ALL
SELECT 'BPD-V6','5','100','100','500'
select * from #myStockFiFo

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#myStockTake','U') IS NOT NULL
DROP TABLE #myStockTake

--===== Create the test table with
CREATE TABLE #myStockTake
        (
        bin varchar(5),
        seq INT,
product varchar(20),
startqty int,
newqty int,
stockdiff int,
runtot int)

INSERT INTO #myStockTake
(bin,seq,product,startqty,newqty,stockdiff,runtot)
SELECT 'AA01A','1','BPD-V6','100','150','50','150' UNION ALL
SELECT 'BB01A','2','BPD-V6','100','100','0','250' UNION ALL
SELECT 'CC01A','3','BPD-V6','150','0','-150','250' UNION ALL
SELECT 'DD01A','4','BPD-V6','150','250','100','500'
select * from #myStockTake

Now, for the Stock Take, bin AA01A, it was expected to contain 100 but actually 150 exist. Increase of 50 and referring to the fifo table, taking the first entry has a value of £140. Thus an increase of 50x £140 = £7,000.
The important bit to note and this is where the logic has changed is that that fifo record (of Unit Value £140) now contains 150 items.
Moving onto bin BB01A, no change. Thus £0.
Bin CC01A has a net loss of 150 items and to calculate the value we should refer to the first available fifo entry which now stands at 150 items @ £140 each (as calculated from the AA01A adjustment). Thus a loss of -150x£140 = -£21,000.
Finally moving onto Bin DD01A, we have a gain of 100 items and referring to the next available fifo entry this stands 100 items @ £50 each (2nd row in fifo the +/-150 of row 1). Thus a gain of 100x£50 = £5,000.
Total -£9,000.

Screenshot from the client's systems:

If you do get time to have a look at this tweak then that'll be great but anyhow thanks once again for your input.
regards,
Dom

...the conundrum now lies in the fact that the fifo quantities change as a result of the stock take....

Dom Horton SSCrazy Points: 2202 More actions · Answer 8

This has been on the backburner for a couple of weeks, and, guess what, I've been asked to amend the fifo logic once again....test code below:
--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#myStockFiFo','U') IS NOT NULL DROP TABLE #myStockFiFo

--===== Create the test table with
CREATE TABLE #myStockFiFo
(
product varchar(20),
seq INT,
startqty int,
value float,
UnitValueGBP float)

INSERT INTO #myStockFiFo
(product,seq,startqty,value,UnitValueGBP)
SELECT 'BPD-V6','1','50','5827','116.54' UNION ALL
SELECT 'BPD-V6','2','50','8000','160' UNION ALL
SELECT 'BPD-V6','3','100','500','5' UNION ALL
SELECT 'BPD-V6','4','500','3','54'
select * from #myStockFiFo

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#myStockTake','U') IS NOT NULL
DROP TABLE #myStockTake

--===== Create the test table with
CREATE TABLE #myStockTake
        (
        bin varchar(5),
        seq INT,
product varchar(20),
startqty int,
newqty int,
stockdiff int
)

INSERT INTO #myStockTake
(bin,seq,product,startqty,newqty,stockdiff)
SELECT 'AA01A','1','BPD-V6','100','150','50' UNION ALL
SELECT 'CC01A','2','BPD-V6','150','0','-150' UNION ALL
SELECT 'CC05A','3','BPD-V6','200','400','200' UNION ALL
SELECT 'DD01A','4','BPD-V6','50','150','100' UNION ALL
SELECT 'GG01A','5','BPD-V6','200','0','-200'
select * from #myStockTake

Transaction 1 - +50 will increase initial fifo record by 50 to bring it up to 100 units @ £116.54 ea = +£5,827.00
Transaction 2 – -150 will now clear the FIRST FIFO record of 50 @ £116.54ea, move on to the next record (2nd FIFO) 50@ £160.00ea, then the next record (3rd FIFO) of 50 @ £5.00 ea = -5827-8000-500 = -£14,077.00
Transaction 3 - +200 will reset back to FIFO record 1 and increase the value – 200 @ £116.54ea = +£23,308.00
Transaction 4 – +100 will reset back to FIFO record 1 and increase the value – 100 @ £116.54ea = +£11,654.00
Transaction 5 – -150 will now clear the FIRST FIFO record of 50 @ £116.54ea, move on to the next record (2nd FIFO) 50@ £160.00ea, then the next record (3rd FIFO) of 100 @ £5.00 ea = -5827-8000-500 = -£14,327.00

FIFO record 4 is unaffected by this process.

Net result:

ChrisM helpfully suggested using a recursive CTE to solve the earlier incarnation of the problem, but truth be told I'm at a loss on how to proceed. To further complicate matters, if a product is accounted for in the stock take but there are no pre-existing FIFO records then we need to refer to a separate table to ascertain the standard or latest price paid (I think I'll get over the initial hurdle before incorporating this logic).
If anyone out there could provide some useful pointers I'd be extremely grateful!