Query - Inventory Turnover by Item Group

#1 · June 4, 2020, 2:24 pm

Here is a great query that lists the Inventory Turnover by Item Group for SAP Business One. You can easily apply a date range to this report if desired or prompt the user for a range of Item Groups.

select g."ItmsGrpNam" as "Item Group Name",
ifnull( sum(case when l."DocDate" >= add_years(current_date, -1)
then l."OutQty" else 0 end) * 100 / nullif(sum((l."InQty" - l."OutQty") * (case when l."DocDate" >= add_years(current_date, -1)
then 0.5 else 1 end)), 0) , 0) as "Inventory Turnover %"
from OITB g left outer join OITM i on g."ItmsGrpCod" = i."ItmsGrpCod"
left outer join OIVL l on l."ItemCode" = i."ItemCode" group
by g."ItmsGrpNam"