Skip to Content

FIFO Stock value for report/query

Feb 01 at 09:17 AM


avatar image


I need to get the stock value for our system to show in queries and reports. I'm currently using the below code but this can take between 15 - 90 seconds to run (we do seem to have an I/O bottleneck on our system which our support company says we need to upgrade our hardware to fix, but we're not in a position to do that at present). Is there a more efficient way I can get this value quickly ?

Select SUM(TransValue) FROM 

(Select SUM(R0.TransValue) TransValue FROM OINM R0 with (no lock) 

INNER JOIN OITW R1 with (no lock) ON R0.ItemCode=R1.ItemCode AND R0.Warehouse=R1.WhsCode 

WHERE R0.DocDate<=CONVERT(date,GetDate(),113) GROUP BY R0.ItemCode 

HAVING SUM(R0.InQty-R0.OutQty)<> 0)T0
SQL | fifo
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Hrvoje Dolenec Feb 01 at 09:55 AM

How about something like this? We are using perpetual inventory and item cost by warehouse, this query shows warehouse status and value on a given date. Posting date is mandatory, item code, item group and warehouse are optional filters.

Note: this is HANA, adjust it for SQL

SELECT T0."ItemCode" AS "Item Code", T1."ItemName" AS "Item Name", T2."ItmsGrpNam" AS "Item Group", T0."LocCode" AS "Whse", SUM(T0."InQty"-T0."OutQty") AS "In Stock Qty", SUM(T0."SumStock") AS "In Stock Value"
FROM OIVL T0 INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode" INNER JOIN OITB T2 ON T1."ItmsGrpCod" = T2."ItmsGrpCod"
WHERE T0."DocDate" <=[%1] AND (T0."ItemCode"=[%2] OR [%2] = '') AND (T2."ItmsGrpNam"=[%3] OR [%3] = '') AND (T0."LocCode"=[%5] OR [%5] = '')
GROUP BY T0."ItemCode", T1."ItemName", T2."ItmsGrpNam", T0."LocCode"
ORDER BY T0."ItemCode" asc, T0."LocCode" asc

Show 1 Share
10 |10000 characters needed characters left characters exceeded

That's perfect. Thanks

Agustin Marcos Cividanes Feb 01 at 10:08 AM


I would not use the OINM because is a view to mantain the compatibility with previous versions of SBO.

I would advice you to use the new tables of log inventory: OILM, OITL, OIVL.

Kind regards


10 |10000 characters needed characters left characters exceeded