Inventory Audit Report
We used the Inventory Audit Report to determine inventory value ending in a specific period. It seems to work okay but it lists every single item in our inventory.
We are looking for more of a summary report. The only option we have for this is by product category. However, we want it a little more detailed than this.
So, is there any way to show the report summarized by our Style #'s? We have have colors and sizes associated with each style. We do not need to know total for each color and size.
We just want the total for each Style #, which is the first four digits of our Item Code. Theses first four digits are unique for each Style. Can a Query be built to just show this summary?
So...would have the following info...
Style #...Total Items...Total Cost...Most Recent Item Cost
Can something like this be built?
Gordon Du replied
Check this one to start, if result is good, date range will be added later:
SELECT SubString(t0.ItemCode,1,4) 'Style #', Sum(t0.OnHand) 'Total Items', Sum(t0.OnHand) *Avg(Case When t1.LastPurPrc = 0 then t0.avgprice else t1.LastPurPrc end) 'Total Cost', Avg(Case When t1.LastPurPrc = 0 then t0.avgprice else t1.LastPurPrc end)'Most Recent Item Cost'
FROM OITW t0
Inner join OITM t1 on t1.itemcode = t0.itemcode
WHERE t0.OnHand > 0
Group By SubString(t0.ItemCode,1,4)