SQL ServerTVVideos
183
0

Buscando huecos entre tus registros (saltos)

Hace unos días uno de nuestros clientes nos pregunto “Maxi: Cómo podemos sacar los registros que están faltando en una tabla (por ej 1,2,5,6) sacar el falta el 3 y 4.

Arme este simple video en mi canal de Youtube en donde te voy a explicar como podes hacer esto con dos técnicas distintas (antes de SQL 2022 y usando GENERATE_SERIES())

Acá te comparto el código que he utilizado

SQL
use AdventureWorks2022 

drop table if exists mi_tabla_small
create table mi_tabla_small (id int, fecha datetime)

insert into mi_tabla_small values (1,getdate()),(3,'20240101' ),(4,'20240102'),(6,'20240103')

select * from mi_tabla_small 

-- buscamos los faltante
DECLARE @start_number INT ;
DECLARE @end_number INT ;

select @start_number = 1
select @end_number = max(id) from mi_tabla_small; -- Buscamos el valor MAximo

declare @numeros as table (n int); -- declaramos variable tabla
	
-- Insertar los números en la tabla temporal
WITH CTE_Numbers AS (
    SELECT @start_number AS n
    UNION ALL
    SELECT n + 1
    FROM CTE_Numbers
    WHERE n < @end_number
)
insert into @numeros -- insertamos los nuneros con CTE
SELECT n
FROM CTE_Numbers 
OPTION (MAXRECURSION 0);

-- Buscamos los gaps
select N from @numeros 
WHERE N NOT IN (SELECT ID FROM mi_tabla_small)

-- Generate_series de SQL 2022

SELECT value as n
			FROM GENERATE_SERIES(1, 10, 1)
	


-------------- Volumen

drop table if exists dbo.mi_tabla 

create table dbo.mi_tabla (id int,
                           c1 char(1000), 
						   c2 char(100),
						   f datetime)

insert into dbo.mi_tabla 
select top 2000000 row_number() over (order by c1.column_id) as nro,
concat('c1_',newid()) as c1,
concat('c2_',newid()) as c2,
getdate()
from sys.columns c1
cross join sys.columns c2

create index ix1 on mi_tabla(id)

-- borramos algunos registros
DELETE FROM mi_tabla
WHERE id IN (
    SELECT TOP 500 id
    FROM mi_tabla where id % 2 = 0
    ORDER BY NEWID()
);




-- Crear una tabla temporal para almacenar los números
CREATE OR ALTER PROCEDURE DBO.GAPS_STD AS

DECLARE @start_number INT ;
DECLARE @end_number INT ;

select @start_number = 1
select @end_number = max(id) from mi_tabla; -- Buscamos el valor MAximo

declare @numeros as table (n int); -- declaramos variable tabla
	
-- Insertar los números en la tabla temporal
WITH CTE_Numbers AS (
    SELECT @start_number AS n
    UNION ALL
    SELECT n + 1
    FROM CTE_Numbers
    WHERE n < @end_number
)
insert into @numeros -- insertamos los nuneros con CTE
SELECT n
FROM CTE_Numbers 
OPTION (MAXRECURSION 0);

-- Buscamos los gaps
select N from @numeros 
WHERE N NOT IN (SELECT ID FROM mi_tabla)


GO

CREATE OR ALTER PROCEDURE DBO.USP_GAP_GENERATE_SERIES
AS
	declare @min_id int,@max_id int
	

	select @min_id = 1
	select @max_id = max(id) from mi_tabla; -- buscamos el maximo

	declare @numeros as table (n int) -- declaramos variable tabla
	
	insert into @numeros -- insertamos los nuneros con GENERATE_SERIES
	SELECT value as n
			FROM GENERATE_SERIES(@min_id, @max_id, 1)
	
	
	SELECT n 
	FROM @numeros
	WHERE n NOT IN (SELECT id FROM mi_tabla) -- Buscamos el GAP
	ORDER BY n;
GO

set statistics  time off

EXEC DBO.USP_GAP_GENERATE_SERIES
EXEC DBO.GAPS_STD 

You must be logged in to post a comment.