Walter,

Thank you for your suggestions and the code.

>Dmitry,
>
>The strategy that I would use is that the stock would be recorded in a single column on the items table (your inv_code column?). Then updating the stock is as simple as updating a single column.
>
>Aside from that, just one transaction table with all the in AND out going transactions. So not two seperate tables, but just one. In this table you've got both the purchases and sales, just indicated by a single column. You might, or might not record the stock in the transaction table for checking consisitency with the items table.
>
>The total stock is then just a lookup in the items table, and the value can be determine by looking at the purchases in the table.
>
>
>in your procedure you can just update the stock for sales with
>
>

>UPDATE t_items SET Stock = Stock  - @nCount WHERE ItemID = @cItemID
>SELECT @Stock = Stock FROM t_items WHERE ItemID = @ItemID
>
>INSERT INTO StockTransActions (ItemID, mutation, Trancode, Trandate, Stock) 
>VALUES(@ItemID, @nCount, 'OUT', getdate(), @Stock)
>

>
>And add Purchases with
>

>UPDATE t_items SET Stock = Stock  + @nCount WHERE ItemID = @cItemID
>SELECT @Stock = Stock FROM t_items WHERE ItemID = @ItemID
>
>INSERT INTO StockTransActions (ItemID, mutation, Transcode, Trandate, Stock, Price) 
>VALUES(@ItemID, @nCount, 'IN', getdate(), @Stock, @Price)
>

>
>Then the stock value can be determined with
>
>

>SELECT ItemID, TotalStock, SUM(CASE WHEN totalstock > mutation+previous THEN (Mutation+Previous) * price 
>		WHEN TotalStock - Previous >0 THEN (TotalStock - Previous) * Price ELSE 0 END) as value
>FROM (SELECT S1.item, S1.Trandate, S1.Mutation, S1.Price , ISNULL(SUM(S2.Mutation),0) As previous,
>    (SELECT Stock FROM t_items WHERE ItemID = S1.ItemID ) as totalstock 
>	FROM StockTransactions S1 LEFT JOIN StockTransactions S2 ON S1.ItemID = S2.ItemID  AND S1.Trandate < S2.Trandate AND S2.TranCode = 'IN'
>	WHERE S1.TranCode = 'IN'
>	GROUP BY S1.itemID, S1.Trandate, S1.Mutation, S1.Price ) as X
>	GROUP BY ItemID, totalStock
>	ORDER BY ItemID
>

>
>Just note that the sales are irrelevant in calculation of the stock value in FIFO. The current stock value is calculated by running the current stock number against the purchases alone (in descending datetime order). That is why the query just looks are records where transcode = 'IN'
>
>* DISCLAIMER * The code written above I wrote from the top of my head, there might be some typos in there.
>
>Walter,
>
>
>
>
>>I don't think you could see my approach from this my reply. This little example is just a segment of the overall solution I am looking for. And actually I do have in my tables a separate record for each In and each Out (my application does not deal with sales and purchase but the concept is similar).
>>I am actually studying your example and it is very helpful, although I don't yet understand everything (clearly I am lacking in the knowledge of SQL select).
>>Here is a summary of what my database structure looks like (a little different from your example)
>>Tables:
>>INVSTOCK - table where all IN-STOCK items are listed. Every receipt is a separate record (pretty much as in your example the type IN record). The QTY_LEFT field stores the current quantity of an item at the UNIT_PRICE that was received.
>>INVUSED - table where the OUT-STOCK items are listed. That is, every time a certain quantity is taken out of the INVSTOCK, a record is added to the table INVUSED.
>>So I have to adapt your approach (in another thread) to my case of two tables. Some things in your approach I sort of understood and already see where it will fit into my approach.
>>One thing I am not seeing in your example, is some variable that stores the value of Items (this is how you refer to them) or Quantity (the term I used). That is, my Stored Procedure where this transaction will be performed will receive a certain value - Quantity - to be "moved" from INVSTOCK to INVUSED. For example, user enters ## (e.g. 7 Items/Quantity) to be taken out of stock and to be used. So I need to traverse the INVSTOCK and on-the-fly create records in INVUSED. At the same time changing the values in INVSTOCK.QTY_LEF (reducing it).
>>I think the solution is somewhere in your example, I just need to learn and find it.
>>
>>>Dmitry,
>>>
>>>With all due respect, you're taking the wrong approach. Yes, you can do this with a do while or even with (ugh) records based solution.
>>>Best is to calculate on the fly as I showed you earlier.
>>>
>>>By having seperate records for purchases and seperate records for sales, you keep way better track of what is happening and is faster than what you're trying to do now and you can calculate the stock in a single SQL statement of 10 lines.
>>>
>>>
>>>
>>>>For now ignore in this sample code the column 'rec_id'
>>>>

>>>>declare @Used decimal (12,2)
>>>>set @Used = 7
>>>>create table #invstock (inv_code char(20), qty_left decimal(10,2), inven_pk int, unit_price decimal (12,2), rec_id char(1))
>>>>go
>>>>insert into #invstock (inv_code, qty_left, inven_pk, unit_price, rec_id) values 
>>>>	('ABC', 15, 1, 1.93, '1')
>>>>insert into #invstock (inv_code, qty_left, inven_pk, unit_price, rec_id) values 
>>>>	('ABC', 4, 2, 1.93, '1')
>>>>insert into #invstock (inv_code, qty_left, inven_pk, unit_price, rec_id) values 
>>>>	('ABC', 7, 3, 1.93, '1')
>>>>insert into #invstock (inv_code, qty_left, inven_pk, unit_price, rec_id) values 
>>>>	('ABC', 8, 4, 1.93, '1')
>>>>

>>>>
>>>>I need to create a SQL Update that will change the value of each record column QTY_LEFT subtracting the value of @Used.
>>>>That is, if the value of @Used is 7, only the first row will change the value of QTY_LEFT to 8. But if the value of @Used in 20, then the first row will have the value of QTY_LEFT as 0, the second row will be 0, and the 3rd one will have QTY_LEFT as 6.
>>>>
>>>>This is one small part of the overall SQL I am trying to create but I need to break it down so that I can understand it.
>>>>
>>>>>Can you provide more details?
>>>>>