Skip to Content
avatar image
Former Member

Sales query including item information and profit

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 %

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Nov 29, 2017 at 02:45 PM

    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]

    Add comment
    10|10000 characters needed characters exceeded