1
Hello I need help to make a query or process to detect gaps in a numerical field, I have a table of tax notes, which has a field num_nfe, it must follow a sequence, there can be no gaps in the numbering and I must check in another field, the status field.
The query should check which records do not exist and also which records have the status field other than "Issued" or "Cancelled".
I’m trying something like this:
SELECT j1.num_nfe +1 AS id
FROM nfe j1
LEFT JOIN nfe j2 ON j2.num_nfe = j1.num_nfe +1
WHERE j2.num_nfe IS NULL
AND (
DATE_FORMAT( j1.dt_emissao, '%Y-%m-%d' )
BETWEEN '2018-03-01'
AND '2018-04-30'
) AND status in('Cancelada','Emitida')
AND j1.num_nfe <> (
SELECT MAX( num_nfe )
FROM nfe)
ORDER BY j1.num_nfe
Can you give us a picture of how you are and how you’d like it to be, just to get a better look at the problem?
– Dherik
I tried to do something like this:
SELECT j1.num_nfe +1 AS id
FROM nfe j1
LEFT JOIN nfe j2 ON j2.num_nfe = j1.num_nfe +1
WHERE j2.num_nfe IS NULL 
AND (
DATE_FORMAT( j1.dt_emissao, '%Y-%m-%d' ) 
BETWEEN '2018-03-01'
AND '2018-04-30'
) AND status in('Cancelada','Emitida')
AND j1.num_nfe <> ( 
SELECT MAX( num_nfe ) 
FROM nfe) 
ORDER BY j1.num_nfe– Victor M.
you can edit your question to show what you have tried and the scenario. So it is easier to view.
– Dherik
select * from nfe Where not exists (select null from nfe nfe2 Where nfe2.num_nfe -1 = nfe.num_nfe) -- there is no previous
– Motta
There is no previous one, but if for example a difference interval with more than one number is inserted it does not detect, for example, if someone instead of typing 10, typing 100 does not show from 11 to 99 for example. and also does not work in case of records that exist but are with different status.
– Victor M.
https://forum.imasters.com.br/topic/237229-falha-na-numera%C3%A7%C3%A3o/ another solution for Oracle , rownum is a pseudo column that I don’t think exists in Mysql
– Motta