Skip to Content
0
Jul 18, 2013 at 01:41 PM

SUB Query

28 Views

Hi Guys,

I have the following Query that I have put together, however I have an addition I need to make using a sub query and I am not sure how to do this (new to sub querys).

I basicaly need to be able to do a sum of T3.Quantity - SUM(t1.Quantity) - in other words I need to get a total of all the quantities for each item on any draft GRNO's created from the Purchase Order and subtract them from the Quantity for that item that was on the PO, I hope that makes sense !

SELECT T2.[DocEntry], T2.[DocNum] AS 'PO Number', T3.[DocDate] AS 'PO Date', T2.[CardCode] AS 'Supplier Code',

T2.[CardName] As 'Supplier', T3.[ItemCode], T3.[Dscription] AS 'Description', T3.[Quantity] as 'PO Quantity', T0.[DocEntry] AS ' Draft GRN No',

T0.[DocDate], T1.[ItemCode], T1.[Quantity] AS 'Received Qty' FROM ODRF T0

INNER JOIN DRF1 ON T0.[DocEntry] = T1.[DocEntry]

LEFT OUTER JOIN OPOR T2 ON T1.[BaseRef] = T2.[DocNum]

LEFT OUTER JOIN POR1 T3 ON T2.[DocEntry] = T3.[DocEntry]

WHERE T1.[BaseRef] = $[$38.44.0] AND T1.[BaseType] = 22 AND T0.[ObjType] = 20

GROUP BY T2.[DocNum], T2.[CardCode], T2.[Cardame], T3.[ItemCode], T3.[Dscription], T3.[Quantity], T0.[Docentry],

T0.[DocDate], T1.[ItemCode], T1.[Quantity], T3.[DocDate], T2.[DocEntry]

I hope that makes sense and I thank you for your input in advance.

Kind regards

Sean