Query for Sales Order Remaining Open Quantity

[former_member224264]

Greetings of the day.

Am trying to create a query for Sales Order, Sum of Row Total of Remaining Open Quantity grouped by BP Code and Open Quantity Total. Want only the Document Number, Card Code and Amount.

SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty], T1.[Price] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDueDate] < (getdate() -14) AND T1.[LineStatus] = 'O' group by T0.[CardCode], T0.[CardName]

Can anyone help me please.

Regards.

[former_member224264]

Greetings of the day mam,

Thanks for your query.

Did modify the query as under and we fetched the results.

SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], Sum(T1.[OpenQty])'Remaining Open Qty', Sum(T1.LineTotal)'Remaining Amount' FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDueDate] < (getdate() -14) AND T1.[LineStatus] = 'O' group by T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName] order by T0.DocNum

Thanks for your support and help.

Regards.