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.

[narayanis · Accepted solution]

Hi Mekhala,

You have to include all fields in group by summing quantity, openqty and price. That is standard SQL rule. While using group by all the fields except formula columns needs to be included. Also, expression T0.[DocDueDate] < (getdate() -14) may not produce correct result, You will need to play on date conversion.

SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], Sum(T1.[Quantity]), Sum(T1.[OpenQty]), Sum(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], T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription]

This will remove error but, will not group data BP code wise because you have included other columns also which maynot be identical to form group of single line.

Regards

[narayanis]

I am glad that I was of some help to you.

[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.