cancel
Showing results for 
Search instead for 
Did you mean: 

Query with Last Invoice Date

lesleysherry7775
Explorer
0 Kudos
128

I am looking to create a report showing

All suppliers

Payment Terms

Current Account Balance

Last purchase invoice date

I can get all fields correct apart from the date of that suppliers last invoice date.

Can anyone assist please?

 

View Entire Topic
LoHa
Active Contributor

Hi,

try this

SELECT
	 OCRD.CardCode
	,OCRD.CardName
	,(
		SELECT
			MAX(T0.DocDate)
		FROM
			OPCH T0
		WHERE
			T0.CardCode = OCRD.CardCode
			AND
			T0.CANCELED = 'N'
	 ) AS [Last purchase invoice date]
FROM 
	OCRD 
WHERE
	OCRD.CardType = 'S'

regards LOthar