Sales query including item information and profit

[Former Member]

Hi experts!

I am looking to update the current query I have for sales reports but have been unable to get exactly what I want so would really appreciate your help. Also my current query doesn't correctly calculate total sales quantity when credits have been made so it would be great if your query does this correctly. For example, invoice for 100kg + Credit of 100kg + invoice for 100kg = Sale of 100kg.

I would like the query to include the following headers

DocNum/PostingDate/BaseDoc/SalesPerson/CardCode/CardName/ItemCode/Dscription/Product Type/Product Category/WhsName/Quantity/Buy Price/Sell Price/Value(FC)/ Value GBP/Margin (GBP)/Haulage pro-rata/Additional costs pro-rata/Net Margin/Net Margin %

[Former Member]

I have managed to put this much together but I am struggling to use the UNION clause to combine invoices with credit notes and also a formula to calculate Net Margin. Please can anyone help?

SELECT T0.[DocNum], T6.[DocNum], T0.[DocDate], T1.[BaseRef] AS 'Base Doc', T0.[DocStatus] AS 'Status', T0.[CardCode] AS 'Cust Code', T0.[CardName] AS 'Customer', T3.[SlpName] AS 'BDM', T1.[ItemCode] AS 'SKU', T1.[Dscription] AS 'Description', T2.[U_PROD_TYPE], T2.[U_FRUIT], T1.[Quantity], T1.[Price] AS 'Sale Price', T1.[Currency], T0.[GrosProfit], T0.[GrosProfFC], T0.[U_Out_Haul_cur], T0.[U_ADD_COSTS]

FROM OINV T0INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

LEFT OUTER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

LEFT OUTER JOIN OSLP T3 ON T0.[SlpCode] = T3.[SlpCode]

LEFT OUTER JOIN IPF1 T4 ON T2.[ItemCode] = T4.[ItemCode]

LEFT OUTER JOIN RIN1 T5 on T5.BaseEntry = T0.DocEntry and T5.BaseLine = T1.Linenum

LEFT OUTER JOIN ORIN T6 on T5.DocEntry = T6.DocEntry

WHERE T0.[DocDate] >=[%0] andT0.[DocDate] <=[%1]

GROUP BY T1.ItemCode, T0.[DocNum], T0.[DocDate], T1.[BaseRef], T0.[DocStatus], T0.[CardCode], T0.[CardName], T3.[SlpName], T1.[Dscription], T2.[U_PROD_TYPE], T2.[U_FRUIT], T1.[Quantity], T1.[Price], T1.[Currency], T0.[GrosProfit], T0.[GrosProfFC], T0.[U_Out_Haul_cur], T0.[U_ADD_COSTS] T6.[DocNum]

ORDER BY T0.[DocDate]