cancel
Showing results for 
Search instead for 
Did you mean: 

Query to show itemised sales analysis

Former Member
0 Kudos

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...

Wendy

View Entire Topic
Former Member
0 Kudos

Hi Wendy,

You can check the below query:

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]'

UNION ALL

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].[ORIN]  T0 INNER JOIN [dbo].[RIN1]  T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]

WHERE T0.[CardCode] =  '[%0]'

Thanks,

Joseph

Former Member
0 Kudos

Exactly what I need, thank you.