Quantity Usage Report

Kurt Wireman

Hello,

We're at year-end and before our Finance team pulled their financial/usage reports, the utility: Set Item/Warehouse PTD and YTD to Zero and the reports reference qty_used_ytd in the items master table (which is now in accurate when looking at the past 12-months). 

Does anyone have a script that gathers usage information (preferably between a rolling 12-month period)? 

If not, which tables would I use to gather this data together? Thanks so much!

0911060553270289.pdf

Ryan Pukanski

Here is a starter script you could try:

SELECT dbo.matltran_mst.item as 'Item P/N',
sum(dbo.matltran_mst.Qty) as TotalQty
FROM dbo.matltran_mst INNER JOIN
dbo.item_mst ON dbo.matltran_mst.site_ref = dbo.item_mst.site_ref AND
dbo.matltran_mst.item = dbo.item_mst.item

Where dbo.matltran_mst.Site_ref='SITE' and dbo.matltran_mst.trans_date between '2020-02-07' and '2023-02-07' and (dbo.matltran_mst.trans_type like '%F%' OR dbo.matltran_mst.trans_type like '%I%' OR dbo.matltran_mst.trans_type like '%R%' OR dbo.matltran_mst.trans_type like '%T%' OR dbo.matltran_mst.trans_type like '%W%' )
group by dbo.matltran_mst.item

This would show you item and qty based on transaction types of finish, issue, receipt, transfer or withdraw. 

Change site and dates to your liking and modify as needed

0712131256598360.doc