In a previous post we counted how many item numbers were in GP using Generic Description as the main category and Short Name as the second level category.

Categories inside categories

Categories inside categories

In this post we take that a little farther. Here we’ll show a SQL Server query that will show our on-hand inventory for each combination of category and subcategory. We assume that all on-hand inventory is located in the GP inventory site named MAIN, and we use the Current Cost from each item card as the value of the item.

[sql]
select
itms.itmgedsc,
itms.itmshnam,
sum(coalesce(qtys.qtyonhnd,0)) as pieces,
sum(itms.currcost * coalesce(qtys.qtyonhnd,0)) as dollars
from dbo.iv00101 itms
left join (select itemnmbr, qtyonhnd from dbo.iv00102 where locncode=’MAIN’) qtys
on itms.itemnmbr=qtys.itemnmbr
group by itmgedsc,itmshnam
order by itmgedsc,itmshnam;
[/sql]

Which results in a report looking something like this:

Sample results

Sample results

Hope this is helpful!

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available