MAX(T0."DocDate") how to use it?

[manager2-ace]

Hello to all of you,

I admit I'm a rookie but I'm trying to learn easy queries on B1.

In this case I need a very simple query that has to list the customers that don't purchase from us since date xx (last purchase date).
In my head was easy, I thought about OINV table and WHERE conditions "from date" - "to date" but the result is not their last purchase and I guess I need to use MAX(T0."DocDate") but I don't know how.

Can someone suggest me the proper syntax for this query, please?

Fields needed are:

Tx."DocNum"

Tx."DocDate"

Tx."CardName"

Thank you all in advance for your help!

[IldiW]

Hi Claudio,

how about this?

SELECT T0.[CardCode]
, MAX(T0.[DocNum]) as 'Last Invoice Number'<br>, MAX(T0.[DocDate]) as 'Last Invoice Date' <br>FROM OINV T0 <br>GROUP BY T0.[CardCode]

Since you are using an aggregate function (MAX), you would need to put everything else in a GROUP BY clause.

Not sure how you will use the list, but it might make sense to add a sorting at the end like:

ORDER BY MAX(T0.[DocDate]) DESC

Hope this helps!

Update - On reflection, this will only give you the last date and the highest (so not necessarily last) invoice number! In our database we have some inherited, older invoices which got entered starting with a 9 so technically they are higher but older).
This is my best effort although I have the feeling others must be able do this much simpler!

SELECT T0.CardCode, I1.MaxDocdate as MaxDocDate, I2.MaxDocNum as MaxInvNum FROM OCRD T0
LEFT JOIN (SELECT x.CardCode, MAX(x.Docdate) as 'MaxDocdate' FROM OINV x GROUP By x.CardCode)  I1 on I1.CardCode=T0.CardCode LEFT JOIN (SELECT  y.CardCode, y.DocDate, MAX(y.DocNum) as 'MaxDocNum' FROM OINV y GROUP By y.CardCode, y.Docdate) I2 on I2.Docdate=I1.MaxDocdate and I2.CardCode=T0.CardCode ORDER BY MAXDocDate desc

Thanks,
Ildi

[manager2-ace]

Thank you Ildi,

I can't make it work the way it's written, maybe because I'm a real beginner and barely understand the syntax.