>>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 >>>>
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.00Now what do we need to calculate the stock value of FIFO.
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 339That 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.PriceThe 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.
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 previousWill sum up the numbers
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 valueThis 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 339Since total stock ( = 332) > 50 + 0, 300 x 50.00 will be added (1st line)
GROUP BY ArticleID, totalStock ORDER BY ArticleIDYou 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