Add field (firstInvoice)

[Gabri]

Hi, I want to add a new field on load data from SQL with name (firstInvoice), for example, I have to tables:

CLIENTS
CLIENTID	NAME
1	AA
2	BB
3	CC
INVOICES
CLIENTID	DATE	AMOUNT
1	07/02/2018	10
2	09/02/2018	15
1	10/05/2019	10
2	15/06/2019	20
2	05/09/2019	20
3	05/01/2020	10
1	15/01/2020	10
1	20/01/2020	20
2	01/02/2020	10
3	04/02/2020	10
3	06/02/2020	20

For each client I need to say  [ SELECT min(year(DATE) from INVOICES) as firstInvoice ]

The desired result is:

CLIENTS
CLIENTID	NAME	firstInvoice
1	AA	2018
2	BB	2018
3	CC	2020

Thanks in advance!

[sunny_talwar]

Does this table also have the same CODCLI  "bd1".dbo.CLIENTS as do "bdi".dbo.CABEFACV? The join will only work if the field values align. For example, if one table has 2, 4, 6 and the other one have 1, 3, 5... the join will not work

[Gabri]

"bd1".dbo.CLIENTS have all clients, so have (1,2,3,4,5.....)

"bdi".dbo.CABEFACV have all invoices, so its possible that have (2,3,3,3,4,8...)

[sunny_talwar]

It should have worked, but I am not sure why it hasn't.

[Gabri]

Hello, I've been testing and finally with a new table, only with (cliendid, firstInvoice), and it works !

[CLIENTESFI]:
LOAD * INLINE [CLIENTID];
SELECT CLIENTID FROM "bd1".dbo.CLIENTES; 
LEFT JOIN(CLIENTESFI)
LOAD CLIENTID, Min(Year(DATE)) as firstInvoice GROUP BY CLIENTID;
SELECT CLIENTID, DATE FROM "bd1".dbo.INVOICES; 

Thanks for your time!

Best regards,

Gabri