>>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>
>BTW, if you want to read my article on this topic with ideas about our procedure, here it is:
>
>
http://social.technet.microsoft.com/wiki/contents/articles/18711.t-sql-fifo-inventory-problem-cost-of-goods-sold.aspxThank you.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham