Hi,
The basic idea of this query is to check whether the quantity transferred to the production whse is being utilised or not.
Select G.itmsgrpNam,a.ItemCode, d.Dscription 'Item Description', Sum(a.PlannedQty) 'SB Qty',(Avg (e.AvgPrice ) * Sum(a.PlannedQty) )as 'SB Value ',Sum (a.IssuedQty) 'Consumed in Production Qty', (Avg (e.AvgPrice )* Sum (a.IssuedQty))'Consumption Value',
Avg(D.Quantity) 'Store Issue Qty',(Avg (e.AvgPrice )* Sum(D.Quantity))'Store Issue Value',
(Sum(a.PlannedQty)-Sum (a.IssuedQty))'Diff. in SB Vs Actual Consumption Qty', ((Sum(a.PlannedQty)-Sum (a.IssuedQty))Avg (e.AvgPrice )) 'Diff. in SB Vs Actual Consumption Value', (Sum (a.IssuedQty)-Sum(D.Quantity)) 'Diff. in Actual Consumption Vs Store Issue in Qty',((Sum (a.IssuedQty)-Sum(D.Quantity)) Avg (e.AvgPrice )) 'Diff. in Actual Consuption Vs Store Issue in Value'
from WOR1 a , OWOR B , OWTR C , WTR1 D , OITW E , OITM F , OITB G
where a.DocEntry = b.DocEntry and c.DocEntry =d.DocEntry and b.PostDate = c.DocDate
and b.PostDate between '20111101' and '20111130' and a.ItemCode = d.ItemCode and g.itmsgrpnam='Chemical' and e.whscode = f.dfltwh and e.ItemCode = f.ItemCode and a.ItemCode = e.ItemCode and a.ItemCode = f.ItemCode and g.ItmsGrpCod = f.ItmsGrpCod
Group by a.ItemCode ,d.Dscription , G.itmsgrpNam
The output is that all the quantities are being repeated. can someone help
Rgds
Kalli