En ciertas ocasiones es necesario extraer de una tabla cuales son los números “continuos” de Facturas o de números de documentos. Esto es útil particularmente por razones de auditoría y de BI. La siguiente consulta realiza dicha operación:
SELECT Principio.NumeroFactura, MIN(final.NumeroFactura)
FROM (SELECT n1.NumeroFactura
FROM dbo.Facturas AS n1
LEFT JOIN dbo.Facturas AS n2
ON n1.NumeroFactura=n2.NumeroFactura+1
WHERE n2.NumeroFactura IS NULL) AS Principio
JOIN (SELECT n1.NumeroFactura
FROM dbo.Facturas AS n1
LEFT JOIN dbo.Facturas AS n2
ON n1.NumeroFactura=n2.NumeroFactura–1
WHERE n2.NumeroFactura IS NULL) AS Final
ON Principio.NumeroFactura <=Final.NumeroFactura
GROUP BY Principio.NumeroFactura
La consulta puede ser intimidante al principio pero en realidad solo usa dos técnicas básicas. La primera técnica puede encontrarse en las sub-consultas internas. Analice la sub-consulta:
SELECT n1.NumeroFactura
FROM dbo.Facturas AS n1
LEFT JOIN dbo.Facturas AS n2
ON n1.NumeroFactura=n2.NumeroFactura+1
WHERE n2.NumeroFactura IS NULL
Se puede ver que usa la combinación de un LEFT JOIN de la tabla con ella misma y un IS NULL para producir los inicios de bloques de facturas. Note que el ON usa NumeroFactura+1 o sea traducido de SQL a español podría ser: liste los NumeroFactura que no tienen (IS NULL) un número anterior consecutivo (ON n1.NumeroFactura=2.NumeroFactura+1). Esta técnica ser repite en la otra subconsulta pero usando un -1, por lo que obtiene los números que no tiene números posteriores consecutivos.
La segunda técnica se emplea para unir estas dos consultas, ya que no tiene ningún elemento identificador en común, por lo que la técnica básicamente une la factura inicial del rango escogiendo el Final del bloque de facturas como la más pequeña de las facturas finales que sea mayor o igual a el mismo. Eliminando las sub-consultas puede leerse:
SELECT Principio.NumeroFactura, MIN(final.NumeroFactura)
FROM Principio
JOIN Final
ON Principio.NumeroFactura <=Final.NumeroFactura
GROUP BY Principio.NumeroFactura
Esta consulta funciona en cualquier versión de SQL (7.0, 2000, 2005 y 2008). Pero en 2005 y 2008 puede hacerse más legible cuando se usan CTEs:
;WITH Principio AS (
SELECT n1.NumeroFactura
FROM dbo.Facturas AS n1
LEFT JOIN dbo.Facturas AS n2
ON n1.NumeroFactura=n2.NumeroFactura+1
WHERE n2.NumeroFactura IS NULL)
, Final AS (
SELECT n1.NumeroFactura
FROM dbo.Facturas AS n1
LEFT JOIN dbo.Facturas AS n2
ON n1.NumeroFactura=n2.NumeroFactura–1
WHERE n2.NumeroFactura IS NULL)
SELECT Principio.NumeroFactura, MIN(final.NumeroFactura)
FROM Principio
JOIN Final
ON Principio.NumeroFactura <=Final.NumeroFactura
GROUP BY Principio.NumeroFactura
