>Dmitry,
>
>
>UPDATE oops the current stock is 232, but I hope you get the point
>
>>>
>>>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,
>>
>>Do you happened to remember or have the thread # of 6 years ago where you posted more on this solution? Your code, in this thread, is way too complicated for me to understand. So I think maybe if I look/study the old discussion I will see the light.
>
>It was on the link that Naomi gave... but I gave the same SQL command. Let me explain how it works.
>
>Consider the structure:
>

>StockID, ArticleID, TranCode, trandate,   Items    Price
> 1        100001      IN       2014-10-22   300   200.00
> 2        100001      OUT      2014-10-23    10
> 3        100001      OUT      2014-11-01   241
> 4        100001      IN       2014-11-03    39   100.00
> 5        100001      RET      2015-01-05     5
> 6        100001      OUT      2015-01-05    70
> 7        100001      IN       2015-01-06   300    50.00

>
>Now what do we need to calculate the stock value of FIFO.
>First we need to calculate the current stock. We can do this with
>
>SELECT SUM(CASE WHEN trancode IN ('IN','RET') THEN items ELSE -Items END ) FROM fifo WHERE articleID = S1.ArticleID
>
>For article 10001 the current stock = 332
>
>Now the trick with FIFO is that if you have the totalstock, you look at the most recent purchases and use the prices from those purchaces to calculate the value.
>Now if we can create a table that looks at the purchases, orders them from recent to least recent and have a running total of the stock like:
>
>

>StockID, ArticleID, TranCode, trandate,   Items    Price  Previous
> 7        100001      IN       2015-01-06   300    50.00        0
> 4        100001      IN       2014-11-03    39   100.00        300
> 1        100001      IN       2014-10-22   300   200.00       339
>

>
>That is done through a self join.
>
>

>SELECT S1.ArticleID, S1.StockID, S1.Items, S1.Price, ISNULL(SUM(S2.Items),0) As previous,
>	FROM fifo S1 
>		LEFT JOIN fifo 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 
>

>
>The left join is only neccesary to calculate all the purchases before (or actually after since we are ordering the purchase lines in descending order) the current purchase line. Now you do not see an ORDER BY clause and actually we do not care how the lines are ordered as long as the previous coulumn is correct.
>The trick is in the LEFT JOIN... lets take a closer look.
>
>

>	FROM fifo S1 
>		LEFT JOIN fifo S2 ON S1.ArticleID = S2.ArticleID  AND S1.Stockid < S2.Stockid  AND S2.TranCode = 'IN'

>
>What it does is for each purchase line in S1, it will join only the S2 purchase lines that are done after the purchase in S1. The
>
>

ISNULL(SUM(S2.Items),0) As previous

>Will sum up the numbers
>
> Since we now have the current stock and the running total of purchases in descending order we can put all together
>
>

>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
>

>
>This part will look at the created table above.
>
>

>StockID, ArticleID, TranCode, trandate,   Items    Price  Previous
> 7        100001      IN       2015-01-06   300    50.00        0
> 4        100001      IN       2014-11-03    39   100.00        300
> 1        100001      IN       2014-10-22   300   200.00       339
>

>
>Since total stock ( = 332) > 50 + 0, 300 x 50.00 will be added (1st line)
>Since total stock ( = 332) < 39 + 300, but total stock 332 - 300 > 0 the value of 32 (332 - 300) * 100 will be added (2nd line)
>For the 3rd line it will add 0 since none of the existing stock comes from that purchase (FIFO).
>
>So the total value = 300 x 50,00 + 32 x 100 = $18.200,00
>
>The group by will run this process for each and every article in the table
>
>

GROUP BY ArticleID, totalStock
>ORDER BY ArticleID

>
>You can limit you focus on one or more articleIDs just by adding it to the WHERE claus
>
>
>

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'  <B> AND ArticleID = 10001 </B>
>	GROUP BY S1.ArticleID, S1.StockID, S1.items, S1.Price ) as X
>GROUP BY ArticleID, totalStock
>

Thank you very much! I will study your description/explanation.