Skip to Content

Show Sales Order with Corresponding Invoice with or without Delivery

Nov 03, 2016 at 01:57 AM


avatar image

Good Day, newbie here

i am trying to get the Sales Order Record via range of Dates, what it should look like:

| CardName | SoDate | SO# | SoAmount | INVDate | INVAmount | INVGrossProfit |

"Where SOAmnt = SUM(RDR1.Price*RDR1.Quantity) and INVAmnt SUM(INV1.Price*INV1.Quantity)"

What i have tried so far is this;

SELECT T0.CardName, T5.SlpName, T0.DocDate [SO Date], T0.DocNum [SO#], (SELECT DISTINCT SUM(T9.Price*T9.Quantity) FROM ORDR T8 INNER JOIN RDR1 T9 ON T8.DocEntry = T9.DocEntry WHERE T8.DocNum = T0.DocNum ) [SOTotal],

--SUM(T1.Quantity*T1.Price) [SO Total],

T3.DocDate [TRA Date], SUM(T3.Quantity*T3.Price) [TRA Amount], SUM(T3.GrssProfit) [Gross Profit] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN DLN1 T2 ON T2.BaseEntry = T1.DocEntry AND T2.BaseLine = T1.LineNum LEFT JOIN INV1 T3 ON T3.BaseEntry = T2.DocEntry AND T3.BaseLine = T2.LineNum LEFT JOIN OINV T4 ON T3.DocEntry = T4.DocEntry RIGHT JOIN OSLP T5 ON T1.SlpCode = T5.SlpCode


GROUP BY T0.DocDate, T3.DocDate,T0.CardName, T5.SlpName, T0.DocNum

ORDER BY T5.SlpName, T0.CardName

What i am getting with this query is that it will only get SO that already have an invoice, what i want is even if there is not yet invoice like if it still on delivery i'll just out "On Delivery" on that null field.

Thanks for your answers in advance

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

dhave barsalote Nov 04, 2016 at 01:20 AM

This solved it.

SELECT DISTINCT T9.SlpName, T1.CardName [Customer Name], T1.DocNum [SO No.], T1.DocDate [SO Date], T1.DocTotal [SO Total], T3.DocNum [Delivery Doc Num], T5.DocNum [TRA No], T5.DocDate [TRA Date], T5.DocTotal [TRA Total], T5.GrosProfit [Gross Profit] FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry left outer join DLN1 T2 on T2.BaseEntry = T0.DocEntry left outer join ODLN T3 on T2.DocEntry = T3.DocEntry left Outer join INV1 T4 on T4.BaseEntry = T3.DocEntry and T4.BaseLine = T2.Linenum and T4.BaseType = 15 OR (T4.Basetype=17 and T4.BaseEntry=T0.DocEntry and T4.BaseLine=T0.LineNum) left outer join OINV T5 on T5.DocEntry = T4.DocEntry left outer join OSLP T9 on T9.SlpCode = T1.SlpCode WHERE T1.[DocDate] BETWEEN '10.01.16' AND '10.27.16' Group by T9.SlpName, T1.[CardName], T1.[DocNum], T1.[DocDate], T1.DocTotal, T3.DocNum, T5.DocNum, T5.DocDate, T5.DocTotal, T5.GrosProfit

10 |10000 characters needed characters left characters exceeded