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

[kothandaraman_nagarajan]

Hi,

Can you explain your process for writing the query?

In general, you need to UNION clause in between to sub query.

For example:

Query A for adding

Query B for subtracting.

Final query will be,

Query A----Sum(qty)

Union all

Query B --Add 'Negative sign(-) in Sum (qty) ie -Sum(Qty)

Hope you understand.

Thanks & Regards,

Nagarajan

Thanks & Regards,

Nagarajan

[Former Member]

Hi Sean,

You can 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.[DocNum] AS ' Draft GRN No',COUNT(*) 'Draft Count',T0.[DocDate],

T1.[ItemCode], SUM(T1.[Quantity]) AS 'Received Qty' ,T3.[Quantity] - SUM(T1.[Quantity]) AS 'Diff Q'
FROM ODRF T0

INNER JOIN DRF1 T1 ON T0.DOCENTRY = T1.DOCENTRY

LEFT OUTER JOIN POR1 T3 ON T1.BASEENTRY = T3.DOCENTRY AND T1.BASETYPE = T3.OBJTYPE AND T1.BASELINE = T3.LINENUM

LEFT OUTER JOIN OPOR T2 ON T3.DOCENTRY = T2.DOCENTRY

WHERE

T0.OBJTYPE = 20

GROUP BY T2.[DocEntry], T2.[DocNum], T3.[DocDate], T2.[CardCode], T2.[CardName], T3.[ItemCode], T3.[Dscription], T3.[Quantity], T0.[DocNum], T0.[DocDate], T1.[ItemCode]

ORDER BY 1 ASC

Let me know if this is your requirement.

Thanks,

Sebastian