Skip to Content
Former Member
Dec 05, 2014 at 04:54 PM

Query to show itemised sales analysis


I know you can get this information from the sales analysis, but I need this for a different purpose. I would like to create a query that shows all invoices and credits of a particular business partner. The query I have so far is:

SELECT T1.[DocEntry] AS 'Doc ID', T0.[DocNum], T0.[TaxDate], T1.[ItemCode], T1.[Dscription] AS 'Item Description', T1.[Quantity], T1.[Price] AS 'Unit Price' FROM [dbo].[OINV] T0 INNER JOIN [dbo].[INV1] T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode] WHERE T0.[CardCode] = [%0]

This gives me the information in exactly the format I need, but it only lists invoices, not credits. Is there any way I can add credits to this query so I can see any items that have been credited?

Thanks in advance...