Numerar colunas com formato específico AAAAMM0001

[frankferdinand]

Olá, tenho uma tabela contendo o campo [Data 1ª visita (dia/mês/ano)] e queria saber como faço para criar uma coluna [ID] com uma numeração sequencial obedecendo o formato AAAAMM0001 (Ano,Mês,Número sequencial)?

Tentei colocando:

LOAD *,

Year([Data 1ª visita (dia/mês/ano)]) & Num(Month([Data 1ª visita (dia/mês/ano)]),'00') & Num(AutoNumber([CNPJ TEXTO]&[Data 1ª visita (dia/mês/ano)]),'0000') as ID

FROM tabela;

Mas eu queria que ele criasse uma sequencia obedecendo ano e mês, ou seja, reiniciar o AutoNumber quando trocar de ano ou mês.

Por exemplo: 2018010001,2018010002,2018020001,2018020002,2018020003,2019010001,2019010002

[lfetensini]

O AutoNumber() irá transformar as linhas em numérico sequencial, mesmo que sejam em campos diferentes.

O "reset" do AutoNumber() só ocorre quando é definido um parâmetro de índice para ele em outro campo, por exemplo:

AutoNumber('NOMEDOINDICE1', CAMPO1) as CAMPO1,
AutoNumber('NOMEDOINDICE2', CAMPO2) as CAMPO2

Para você "resetar" o AutoNumber em um mesmo campo, acredito que só seria possível se você fizesse um laço de repetição de acordo com a combinação de Ano/Mês. Uma espécie de For Each e Left Join. Não sei se o esforço vale a pena.

Então um truque seria fazer autonumber em uma situção "variável":

Year([Data 1ª visita (dia/mês/ano)])
 & Num(Month([Data 1ª visita (dia/mês/ano)]),'00')
 & Num(AutoNumber(chr(39) & Year([Data 1ª visita (dia/mês/ano)]) & Num(Month([Data 1ª visita (dia/mês/ano)]),'00') & chr(39), Year([Data 1ª visita (dia/mês/ano)]) & Num(Month([Data 1ª visita (dia/mês/ano)]),'00')), '0000') as AutoTESTE

[frankferdinand]

Obrigado @lfetensini ! Quase chegamos lá. Testei conforme abaixo:

LOAD *,
Year(Data) & Num(Month(Data),'00')
& Num(AutoNumber(chr(39) & Year(Data) & Num(Month(Data),'00') & chr(39), Year(Data) & Num(Month(Data),'00')), '0000') as AutoTESTE;

LOAD * INLINE

[
NomeEmpresa, Data
Empresa01, 05/01/2018
Empresa02, 16/01/2018
Empresa03, 25/01/2018
Empresa04, 26/01/2018
Empresa05, 02/02/2018
Empresa06, 10/02/2018
Empresa07, 20/02/2018
Empresa08, 09/03/2018
Empresa09, 16/04/2018
Empresa10, 06/01/2019
Empresa11, 30/01/2019
Empresa12, 31/01/2019
];

Mas a contagem ficou estática. Veja:

AutoTESTE NomeEmpresa Data
2018010001 Empresa01 05/01/2018
2018010001 Empresa02 16/01/2018
2018010001 Empresa03 25/01/2018
2018010001 Empresa04 26/01/2018
2018020001 Empresa05 02/02/2018
2018020001 Empresa06 10/02/2018
2018020001 Empresa07 20/02/2018
2018030001 Empresa08 09/03/2018
2018040001 Empresa09 16/04/2018
2019010001 Empresa10 06/01/2019
2019010001 Empresa11 30/01/2019
2019010001 Empresa12 31/01/2019

No entanto, gostaria que ficasse assim:

AutoTESTE NomeEmpresa Data
2018010001 Empresa01 05/01/2018
2018010002 Empresa02 16/01/2018
2018010003 Empresa03 25/01/2018
2018010004 Empresa04 26/01/2018
2018020001 Empresa05 02/02/2018
2018020002 Empresa06 10/02/2018
2018020003 Empresa07 20/02/2018
2018030001 Empresa08 09/03/2018
2018040001 Empresa09 16/04/2018
2019010001 Empresa10 06/01/2019
2019010002 Empresa11 30/01/2019
2019010003 Empresa12 31/01/2019

Permaneço no aguardo de uma solução.