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!

[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)