Skip to Content
0
Dec 05, 2011 at 05:17 PM

Query Problem

29 Views

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