Find invoice number gaps - Buscar huecos en números de facturación

invoice_gaps.sql

/*
Table name: documentos_facturas
Number field: numero
Sample data numbers: 1, 2, 3, 6, 9
*/

/* Find just the the first gap for invoice number - El número que correspondería al primer hueco */

SELECT MIN(a.numero)+1 AS next_id
FROM documentos_facturas a
LEFT JOIN documentos_facturas b ON a.numero=b.numero-1
WHERE b.numero IS NULL

/* RETURNS: 

next_id = 4

*/
 
/* All gaps with their ranges - Todos los huecos con sus rangos */
 
SELECT (t1.numero + 1) as gap_starts_at, 
(SELECT MIN(t3.numero) -1 FROM documentos_facturas t3 WHERE t3.numero > t1.numero) as gap_ends_at 
FROM documentos_facturas t1 
WHERE NOT EXISTS (SELECT t2.numero FROM documentos_facturas t2 WHERE t2.numero = t1.numero + 1) 
HAVING gap_ends_at IS NOT NULL

/* RETURNS:  

gap_starts_at = 4 / gap_ends_at = 5 
gap_starts_at = 7 / gap_ends_at = 8 

*/