Query

[Former Member]

I want query to capture the Quantity purchased,Quantity Recevied, Quantity Retruned

[Former Member · Accepted solution]

Try this:

SELECT T0.ItemCode, SUM(T0.Quantity) 'PO Qty',SUM(T1.Quantity) 'GRPO Qty', SUM(T2.Quantity) 'Return Qty'

FROM dbo.POR1 T0

LEFT JOIN dbo.PDN1 T1

ON T0.DocEntry = T1.BaseEntry AND T0.LineNum = T1.BaseLine

LEFT JOIN dbo.RPD1 T2

ON T1.DocEntry = T2.BaseEntry AND T1.LineNum = T2.BaseLine

WHERE T0.ItemCode is not NULL AND T0.DocDate >= [%0\] AND T0.DocDate <= [%1\]

GROUP BY T0.ItemCode

Thanks,

Gordon

[Former Member · Accepted solution]

THANK YOU VERY MUCH FOR YOUR HELP

[former_member325312]

Hi Prem

chek the query below i think this will solve ur prob

SELECT T0.[DocNum][PONO], T1.[ItemCode], T1.[Dscription], T1.[Quantity][Purchased Qty],

T3.[Quantity][Recieved Qty],T5.[Quantity][Returned Qty], T2.[DocNum][GRPO No], T4.[DocNum][GoodsReturn No]

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry,

OPDN T2 INNER JOIN PDN1 T3 ON T2.DocEntry = T3.DocEntry,

ORPD T4 INNER JOIN RPD1 T5 ON T4.DocEntry = T5.DocEntry

WHERE T3.[BaseRef] = T0.[DocNum] and T5.[BaseRef] = T2.[DocNum]

GROUP BY T0.[DocNum], T1.[ItemCode], T1.[Dscription], T1.[Quantity],

T3.[Quantity],T5.[Quantity], T2.[DocNum], T4.[DocNum]

cheers

Jenny