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.

[IldiW]

Hi Claudio,

how about this one?

SELECT T0.[CardCode], T0.[CardName],  O.DOCDATE as 'Last Invoice Date', O.DocNum as 'Last Doc Num'
FROM OCRD T0  
INNER JOIN OINV O ON O.CARDCODE = T0.CARDCODE
WHERE T0.[CardType] = 'C' and  T0.[validFor]  = 'Y' 
AND O.DOCNUM = (select max(R.Docnum) from OINV R where R.[CardCode] = T0.CARDCODE)
GROUP BY T0.[CardCode], T0.[CardName],  O.DOCDATE , O.Docnum
ORDER BY T0.CARDCODE

It should give you a list of BP Code, BP Name, Last Invoice date and Last Invoice number - where the BP type is Customer and the BP is Active.
Thanks,

Ildi

[manager2-ace]

Thank you very much Ildi, this works great!

[kai_f]

Hi,

if you want Customers you need to connect your OINV table with the OCRD table.

Maybe something like:

select a2.DocNum, a2.DocDate, a2.CardName 
from OCRD as a1 inner join OINV as a2 
ON a1.CardCode = a2.CardCode 
where a1.CardCode IN 
(
Select distinct CardCode 
from OINV 
where DocDate > DATEADD(year, -1, getdate())
)

[manager2-ace]

Thank you Kai,

customers are already into OINV table and they are correctly recalled in other queries so I don't see the need to add the OCRD table.

I just need to know how to use the MAX(docdate) instruction in the following query draft:

select T0."DocNum", T0."DocDate", T0."CardName"

from OINV T0

where ______

[kai_f]

Ok, maybe you are looking for something like this:

select DocNum, DocDate, CardName 
from OINV 
group by DocNum, DocDate, CardName 
having MAX(DocDate) > DATEADD(year,-1,GETDATE())

[manager2-ace]

Thank you again Kai 🙂

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

Moreover, I don't know where to find the "having" on your last line ...

[kai_f]

Are you using Query Generator in B1 or Management Studio/Visual Studio?

Query Generator: Add the HAVING inside the 'Group By' field

MS/VS: Execute the query directly on your db

If it still does not work please post your error message

[manager2-ace]

Good morning Kai,

I'm using the query generator.

Here attached the query syntax and the error

Thanks for your time!

[LoHa]

Hi

in your having is MAX(DocDate) it should be MAX(T0."DocDate") and GetDate is not known in HANA it shoul be CURRENT_DATE,DATEADD is ADD_YEARS

something like this

HAVING max(T0."DocDate") > ADD_YEARS(CURRENT_DATE,-1)