Historical Quantities on Hand of a part

unknown

Is there a way to determine the quantities on hand of a particular part on a particular date?  Is there historical data somewhere?

unknown

Do you have access to the database?

Can you run SQL against it?

If so, this will give you the effect on the qty on hand over time - you just need to add a running total column to the results.

In the sql below I pick on one part - part id = 10001.





/*AUTH:MMKING

DATE:20160923

STATUS:KNOWN GOOD

PURPOSE:SHOW QTY ON HAND OVER TIME

*/

select a.part_id,

b.DESCRIPTION,

a.transaction_date,

sum(case when a.TYPE = 'I' then QTY ELSE QTY * -1 END) AS EFFECT_ON_QTY_ON_HAND,

sum(case when a.TYPE = 'I' then COSTED_QTY ELSE COSTED_QTY * -1 END) AS THESE_NEED_COSTING_IF_NONZERO,

sum(case when a.TYPE = 'I' then ACT_MATERIAL_COST ELSE ACT_MATERIAL_COST * -1 END) AS COST

from inventory_trans a,

part b

where a.part_id = b.id

and a.part_id = '10001'

group by part_id, b.DESCRIPTION,a.transaction_date

order by a.transaction_date;