Hello, I need help to make a query or procedure to detect gaps in a numeric field, I have a fiscal invoice table, which has a num_nfe field, it must follow a sequence, there can not be gaps in the numbers 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 "Canceled."
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