>>I am attempting to convert VFP code into a stored procedure. Here is the pseudo-summary of how it works:
>>There are two tables:
>>Table 1 – Parts Stock Quantity and Prices. Example:
>>

>>PART #     QTY      PRICE    PK VAL
>>001           2           $4          1
>>001           1           $2          2
>>001           4           $1          3
>>

>>
>>Table 2 – Parts Used and Prices. Example of structure:
>>

>>PART #     QTY USED   PRICE
>>--------        -------------      -------
>>

>>The procedure (in VFP and in SQL Stored Procedure) will receive a Quantity Used (e.g. 5)
>>
>>I need to process this quantity against the Table 1 and reduce the stock quantities, and at the same time add a record in the Table 2 of used quantities and prices. For example, the Table 2 after processing Used quantity 5 would have the following records
>>

>>PART #    QTY USED   PRICE
>>--------     -------------    -------
>>001         2                     $4
>>001        1                      $2
>>001         3                     $1
>>

>>
>>And the table 1 would have the following values:
>>
>>

>>PART #     QTY      PRICE    PK VAL
>>001           0           $4          1
>>001           0           $2          2
>>001           1           $1          3
>>

>>
>>In VFP this was done with a DO WHILE and other procedural code. What approach do I need for doing the same in SQL Server stored procedure?
>
>It is a tricky problem in SQL Server. There are several articles about this problem and I've written a complex procedure for our inventory.
>
>Start your reading from this article:
>
>https://ask.sqlservercentral.com/questions/1961/the-fifo-stock-inventory-sql-problem.html

It might be less tricky than you think. I posted an answer (though the thread is 6 years old)

SELECT ArticleID, TotalStock, SUM(CASE WHEN totalstock > Items+previous THEN (Items+Previous) * price 
		WHEN TotalStock - Previous >0 THEN (TotalStock - Previous) * Price ELSE 0 END) as value
FROM (SELECT S1.ArticleID, S1.StockID, S1.Items, S1.Price, ISNULL(SUM(S2.Items),0) As previous,
    (SELECT SUM(CASE WHEN trancode IN ('IN','RET') THEN items ELSE -Items END ) FROM fifo2 WHERE articleID = S1.ArticleID) as totalstock 
	FROM fifo2 S1 LEFT JOIN fifo2 S2 ON S1.ArticleID = S2.ArticleID  AND S1.Stockid < S2.Stockid  AND S2.TranCode = 'IN'
	WHERE S1.TranCode = 'IN'
	GROUP BY S1.ArticleID, S1.StockID, S1.items, S1.Price ) as X
	GROUP BY ArticleID, totalStock
	ORDER BY ArticleID

The trick is that you first calculate the current stock. Using that, you traverse back the purchaches from most recent to old. And substract the purchases from the stock until it is zero and calculate the values in the same process.

For dmitry, I'd advise to use the same database structure as in the example and just calculate the value on the fly. The routine above process 1M records in just 3 seconds on my laptop,I expect that to be fast enough.

Walter,