SUB Query

[sean_martin_fl]

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

[KennedyT21 · Accepted solution]

Hi Sean Martin

Try this

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' ,

NetQty=( (Select Sum(c.[Quantity]) from por1 c where c.docentry=T3.[DocEntry] and c.Itemcode=t3.itemcode ) -

T1.[Quantity] )

FROM ODRF T0

INNER JOIN DRF1 T1 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]

Check with out Group by

Hope helpful

Regards

Kennedy

[sean_martin_fl · Accepted solution]

Hi Kennedy,

Many thanks for that, was perfect, I understand now how you constructed that, very helpful.

Kind regards

Sean