Skip to Content
avatar image
Former Member

Show Sales Order with Corresponding Invoice with or without Delivery

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

WHERE T4.DocNum IS NOT NULL AND T0.DocDate BETWEEN '' AND ''

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    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

    Add comment
    10|10000 characters needed characters exceeded