Imagine two warehouses with the same shelf value: 200 million in inventory. The first moves that merchandise six times a year; the second, barely once. On paper they are worth the same, but the second hides a serious problem: frozen capital, aging products, and cash that never returns to the register. The inventory turnover in SAP Business One It is precisely the indicator that separates a healthy warehouse from one that drains your company's liquidity.
In this tutorial you will learn to calculate that indicator directly from your database, with SQL queries ready to copy and paste, in SQL Server and SAP HANA versions. We will not talk about abstract financial theory. On the contrary, I will show you how to convert SAP movement tables into a clear traffic light that tells you which products turn over, which ones sleep, and how much cash you have tied up right now.

Why you need to measure inventory turnover in your company
Inventory is, probably, the most silent investment in your business. It does not appear as an obvious expense on the income statement, but it consumes warehouse space, insurance, financing, and, above all, working capital. Consequently, every product that does not move is parked money that could be funding growth, paying off debt, or improving your cash position.
Inventory turnover answers a very specific business question: how many times do I sell and replenish my stock during a period? For example, a turnover of 8 means that you renew your inventory eight times a year; a turnover of 1 means that the merchandise remains in the warehouse for an average of twelve months before leaving. Therefore, the higher the turnover, the more efficient the use of your capital.
In addition, this indicator is the basis for critical decisions: reorder policies, vendor negotiations, discounts to clear slow-moving stock, and cash flow projection. However, many companies that use SAP Business One never calculate it because they assume it requires an expensive BI module. The truth is that all the information is already in your database, waiting for a good SQL query.
Prerequisites before starting
Before executing the queries, make sure you meet three conditions. First, you need access to the SAP Business One Query Generator (Tools > Queries > Query Generator) or SQL Server Management Studio / HANA Studio with read permissions on your company database.
Secondly, you need to know which database engine your installation uses: SQL Server or SAP HANA. This is crucial because the syntax changes. Therefore, below you will find each query in both versions. Thirdly, it is helpful to be clear about the analysis period; in these examples we will use the last twelve months, but you can easily adjust it.
For reference, these are the tables we will use: OINM (inventory log, where every incoming and outgoing movement is recorded with its value), OITM (Item Master), OITW (stock by item and warehouse) and OITB (item groups). In this way, we will combine historical movement with current stock to obtain the index.
Step-by-step tutorial: calculate inventory turnover in SAP Business One
The logic of the calculation is simple, although it is worth understanding well. The turnover rate is obtained by dividing the cost of the outings from a period between average inventory value. In practice, the cost of issues is equivalent to the cost of goods sold, which in SAP is recorded in the column TransValue from the OINM table every time a product leaves the warehouse.
Next, we will separate the calculation into two blocks to avoid row multiplication errors: first we add the cost of issues per item, then we calculate the current inventory value, and finally we combine them. This way, the query is accurate and easy to maintain. As a result, you will also obtain the average days of inventory and a classification traffic light.
inventory turnover by product
Dónde ejecutar este query. Úsalo en SQL Server Management Studio o en HANA Studio. El Generador de consultas de SAP Business One no siempre digiere las expresiones de tabla común (WITH), así que si necesitas el resultado dentro de B1, guarda la consulta como vista y llama a la vista desde el Generador.
SQL Server
-- Inventory Turnover by Product (Last 12 Months) - SQL Server
WITH Costs AS (
SELECT ItemCode, SUM(TransValue) AS CostOfSales
FROM OINM
WHERE OutQty > 0
AND DocDate >= DATEADD(MONTH, -12, GETDATE())
GROUP BY ItemCode
),
Inventory AS (
SELECT T1.ItemCode, T1.ItemName, T1.AvgPrice,
SUM(T2.OnHand) AS ActualStock,
SUM(T2.OnHand) * T1.AvgPrice AS InventoryValue
FROM OITM T1
INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode
GROUP BY T1.ItemCode, T1.ItemName, T1.AvgPrice
)
SELECT
I.ItemCode,
I.ItemName,
ISNULL(C.CostOfSales, 0) AS 12M_Cost_Of_Sales,
I.Actual_Stock,
I.Inventory_Value,
CASE WHEN I.Inventory_Value > 0
THEN ISNULL(C.CostOfGoodsSold, 0) / I.InventoryValue
ELSE 0 END AS InventoryTurnoverRatio,
CASE WHEN ISNULL(C.CostOfGoodsSold, 0) > 0
THEN I.InventoryValue / (C.CostOfSales / 365.0)
ELSE NULL END AS Inventory_Days,
CASE
WHEN ISNULL(C.CostOfSales, 0) = 0 THEN 'D - No movement'
WHEN C.CostOfSales / I.InventoryValue >= 6 THEN 'A - High turnover'
WHEN C.CostOfSales / I.InventoryValue >= 2 THEN 'B - Average Turnover'
ELSE 'C - Low Turnover' END AS TrafficLight
FROM Inventory I
LEFT JOIN Costs C ON I.ItemCode = C.ItemCode
WHERE I.InventoryValue > 0
ORDER BY TurnoverIndex ASC;
SAP HANA
-- Inventory Turnover by Product (Last 12 Months) - SAP HANA
WITH Costs AS (
SELECT "ItemCode", SUM("TransValue") AS "CostOfSales"
FROM OINM
WHERE "OutQty" > 0
AND "DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)
GROUP BY "ItemCode"
),
Inventory AS (
SELECT T1."ItemCode", T1."ItemName", T1."AvgPrice",
SUM(T2."OnHand") AS "ActualStock",
SUM(T2."OnHand") * T1."AvgPrice" AS "InventoryValue"
FROM OITM T1
INNER JOIN OITW T2 ON T1."ItemCode" = T2."ItemCode"
GROUP BY T1."ItemCode", T1."ItemName", T1."AvgPrice"
)
SELECT
I."ItemCode",
I."ItemName",
IFNULL(C."CostOfSales", 0) AS "12M_Cost_Of_Sales",
I."CurrentStock",
I."InventoryValue",
CASE WHEN I."InventoryValue" > 0
THEN IFNULL(C."CostOfGoodsSold", 0) / I."InventoryValue"
ELSE 0 END AS "Inventory_Turnover_Ratio",
CASE WHEN IFNULL(C."CostOfGoodsSold", 0) > 0
THEN I."InventoryValue" / (C."CostOfGoodsSold" / 365.0)
ELSE NULL END AS "InventoryDays",
CASE
WHEN IFNULL(C."Cost_of_Sales", 0) = 0 THEN 'D - No movement'
WHEN C."Cost_of_Sales" / I."Inventory_Value" >= 6 THEN 'A - High turnover'
WHEN C."CostOfSales" / I."InventoryValue" >= 2 THEN 'B - Average Turnover'
ELSE 'C - Low Turnover' END AS "TrafficLight"
FROM Inventory I
LEFT JOIN Costs C ON I."ItemCode" = C."ItemCode"
WHERE I."InventoryValue" > 0
ORDER BY "Turnover_Index" ASC;
Observe the structure: the block Costs sum up the cost of everything that came out in twelve months; the block Inventory calculate the current stock value using the average costAverage Price); and the final query joins them. For example, if an item had 50 million in issues and keeps 10 million in stock, its index is 5, meaning it turned over five times. In addition, the column Inventory_Days Translate that index into something more intuitive for management: average days to sell.

Inventory turnover variations and use cases
The main query works at the product level, but management often needs a more aggregated view. For example, comparing turnover between business lines to decide where to concentrate capital. Below is a variation that groups the index by item group, ideal for a committee meeting.
Inventory turnover by item group
SQL Server
-- Turnover by Item Group - SQL Server
WITH Costs AS (
SELECT ItemCode, SUM(TransValue) AS CostOut
FROM OINM
WHERE OutQty > 0
AND DocDate >= DATEADD(MONTH, -12, GETDATE())
GROUP BY ItemCode
),
Inventory AS (
SELECT T1.ItemCode, T1.ItmsGrpCod,
SUM(T2.OnHand) * T1.AvgPrice AS InventoryValue
FROM OITM T1
INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode
GROUP BY T1.ItemCode, T1.ItmsGrpCod, T1.AvgPrice
)
SELECT
T3.ItemsGroupName AS Group,
SUM(ISNULL(C.CostOfGoodsSold, 0)) AS 12M_Cost_Of_Goods_Sold,
SUM(I.InventoryValue) AS Inventory_Value,
CASE WHEN SUM(I.InventoryValue) > 0
THEN SUM(ISNULL(C.CostOfGoodsSold, 0)) / SUM(I.InventoryValue)
ELSE 0 END AS Group_Turnover
FROM Inventory I
INNER JOIN OITB T3 ON I.ItemsGroupCode = T3.ItemsGroupCode
LEFT JOIN Costs C ON I.ItemCode = C.ItemCode
GROUP BY T3.ItemsGroupName
ORDER BY GroupTurnover ASC;
SAP HANA
-- Turnover by Item Group - SAP HANA
WITH Costs AS (
SELECT "ItemCode", SUM("TransValue") AS "CostOfSales"
FROM OINM
WHERE "OutQty" > 0
AND "DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)
GROUP BY "ItemCode"
),
Inventory AS (
SELECT T1."ItemCode", T1."ItmsGrpCod",
SUM(T2."OnHand") * T1."AvgPrice" AS "InventoryValue"
FROM OITM T1
INNER JOIN OITW T2 ON T1."ItemCode" = T2."ItemCode"
GROUP BY T1."ItemCode", T1."ItmsGrpCod", T1."AvgPrice"
)
SELECT
T3."ItemsGroupName" AS "Group",
SUM(IFNULL(C."CostOfSales", 0)) AS "12M_Cost_Of_Sales",
SUM(I."InventoryValue") AS "Inventory_Value",
CASE WHEN SUM(I."InventoryValue") > 0
THEN SUM(IFNULL(C."Cost_of_Goods_Sold", 0)) / SUM(I."Inventory_Value")
ELSE 0 END AS "Group_Turnover"
FROM Inventory I
INNER JOIN OITB T3 ON I."ItemsGroupCode" = T3."ItemsGroupCode"
LEFT JOIN Costs C ON I."ItemCode" = C."ItemCode"
GROUP BY T3."ItemsGroupName"
ORDER BY "GroupTurnover" ASC;
Detect dead stock tied-up capital
There is a use case that usually generates the greatest impact in a management meeting: quantifying dead stock. That is, products that still have inventory but have recorded no sales in the last twelve months. Despite the usual sales optimism, this query usually reveals uncomfortable figures of frozen capital.
SQL Server
-- Dead stock: items in stock with no sales in the last 12 months - SQL Server
SELECT
T1.ItemCode,
T1.ItemName,
SUM(T2.OnHand) AS Actual_Stock,
SUM(T2.OnHand) * T1.AvgPrice AS Fixed_Asset_Value,
T3.Last_Out
FROM OITM T1
INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode
LEFT JOIN (
SELECT ItemCode, MAX(DocDate) AS LastOut
FROM OINM WHERE OutQty > 0
GROUP BY ItemCode
) T3 ON T1.ItemCode = T3.ItemCode
WHERE T2.OnHand > 0
AND (T3.LastIssue IS NULL OR T3.LastIssue < DATEADD(MONTH, -12, GETDATE()))
GROUP BY T1.ItemCode, T1.ItemName, T1.AvgPrice, T3.LastIssue
ORDER BY Fixed_Asset_Value DESC;
SAP HANA
-- Dead stock: items in stock with no sales in the last 12 months - SAP HANA
SELECT
T1."ItemCode",
T1."ItemName",
SUM(T2."OnHand") AS "Actual_Stock",
SUM(T2."OnHand") * T1."AvgPrice" AS "Fixed_Asset_Value",
T3."Last_Issue"
FROM OITM T1
INNER JOIN OITW T2 ON T1."ItemCode" = T2."ItemCode"
LEFT JOIN (
SELECT "ItemCode", MAX("DocDate") AS "LastOut"
FROM OINM WHERE "OutQty" > 0
GROUP BY "ItemCode"
) T3 ON T1."ItemCode" = T3."ItemCode"
WHERE T2."OnHand" > 0
AND (T3."LastIssue" IS NULL OR T3."LastIssue" < ADD_MONTHS(CURRENT_DATE, -12))
GROUP BY T1."ItemCode", T1."ItemName", T1."AvgPrice", T3."LastIssue"
ORDER BY "Fixed Assets" DESC;
As a result, you will have a list prioritized by amount: precisely the argument you need to drive liquidations, promotions, or purchasing adjustments. For example, presenting «there are 80 million tied up in products that haven't sold in over a year» completely changes the tone of a conversation with owners or general management.
Best practices and common mistakes
First of all, watch the cost you are using. These queries are based on Average Price (average cost); if your company values inventory using FIFO or standard cost, adjust the logic to reflect it, otherwise the inventory value will be distorted. Also, remember that the «ideal» turnover is not universal: a perishable food distributor should turn over inventory much faster than a heavy machinery manufacturer.
Second, avoid a common mistake: directly joining OINM with OITW without separating the calculations. However, doing so multiplies the rows and distorts the inventory value. That is why we use independent WITH blocks. Third, define a representative period; twelve months smooths out seasonality, but you might need quarterly analyses in highly dynamic businesses.
Finally, do not leave the indicator trapped in a query. Take it to a formatted search, a dashboard, or a recurring report. This way, turnover stops being anecdotal data and becomes a management habit that your team reviews every month.

Official SAP Resources
To dive deeper, SAP offers specific documentation on the inventory turnover analysis report on its help portal (help.sap.com), within the Inventory Turnover Analysis section of the Inventory module. In addition, the official SAP community (community.sap.com) maintains very useful threads on calculating accumulated values from OINM, which are worth reviewing if you need accounting precision.
Conclusion: turn your inventory into decisions
To measure the inventory turnover in SAP Business One It is not an academic exercise; it is one of the most direct ways to free up working capital without borrowing a single penny from the bank. With three SQL queries, you already have the index by product, the comparison by group, and the dead stock map. Therefore, the next step is up to you.
My recommendation is specific: run the main query in your database today and look at the ten slowest-moving products. You will probably find dormant money that no one was seeing. And if you want to turn this into a permanent dashboard, with formatted searches and automated reports, that is exactly the kind of implementation that distinguishes a company that uses SAP from one that takes full advantage of it.




