InforSupports

Duplicados y calendario recursivo: dos técnicas SQL útiles

Written by

Francisco

in

Dos problemas frecuentes en el día a día con SQL: detectar registros que se repiten cuando no deberían, y generar un calendario completo (incluyendo los días sin actividad) para auditar si un proceso programado corrió todos los días que debía. Ambos se resuelven con técnicas de SQL avanzado que vale la pena tener siempre a mano.

Contexto del problema

Partiendo de una tabla que registra movimientos (por ejemplo, renovaciones de un registro que debería ocurrir una sola vez por año), se detectó que algunos registros tenían más de un movimiento del mismo tipo en el mismo año — algo que, por reglas de negocio, no debería pasar.

1. Ver el detalle completo de los registros duplicados

SELECT 
    r.codigo_registro,
    r.tipo_movimiento,
    r.fecha_carga,
    r.cliente,
    r.producto,
    r.monto,
    r.vigencia_inicio,
    r.vigencia_fin
FROM movimientos r
WHERE r.tipo_movimiento = 'Renovacion'
  AND r.fecha_carga >= '2025-01-01'
  AND r.codigo_registro IN (
        SELECT codigo_registro
        FROM movimientos
        WHERE tipo_movimiento = 'Renovacion'
          AND fecha_carga >= '2025-01-01'
        GROUP BY codigo_registro
        HAVING COUNT(*) > 1
  )
ORDER BY r.codigo_registro, r.fecha_carga;

La lógica en dos pasos: la subconsulta (dentro del IN) identifica primero qué códigos de registro tienen más de un movimiento del tipo buscado (usando GROUP BY + HAVING COUNT(*) > 1), y la consulta externa trae el detalle completo de esos registros específicos, ordenados para poder comparar visualmente las fechas de cada duplicado.

2. Agrupar por año para dimensionar el problema

SELECT 
    r.codigo_registro,
    YEAR(r.fecha_carga) AS Anio,
    COUNT(*) AS Cantidad
FROM movimientos r
WHERE r.tipo_movimiento = 'Renovacion'
  AND r.fecha_carga >= '2025-01-01'
GROUP BY r.codigo_registro, YEAR(r.fecha_carga)
HAVING COUNT(*) > 1;

Una vista rápida y resumida, útil antes de bucear al detalle: cuántos registros están duplicados, y en qué años específicamente ocurre — sin traer todavía toda la información de cada fila.

3. Detectar duplicados con funciones de ventana

SELECT *
FROM (
    SELECT 
        r.*,
        ROW_NUMBER() OVER (PARTITION BY r.codigo_registro, YEAR(r.fecha_carga) ORDER BY r.fecha_carga) AS rn
    FROM movimientos r
    WHERE r.tipo_movimiento = 'Renovacion'
      AND r.fecha_carga >= '2025-01-01'
) t
WHERE rn > 1;

Esta es una forma alternativa (y en muchos casos más flexible) de lograr lo mismo que el primer query, pero sin subconsulta. ROW_NUMBER() numera cada fila dentro de su partición (codigo_registro + año), en orden de fecha. La primera aparición de cada combinación recibe rn = 1; cualquier repetición adicional recibe 2, 3, etc. Filtrando por rn > 1, el resultado son exactamente los duplicados sobrantes — es decir, todo lo que vendría «de más» después del primer registro legítimo. Esta forma es la base para, además de detectar, poder eliminar duplicados fácilmente (borrando directamente las filas con rn > 1).

4. Generar un calendario completo con CTE recursivo

Un caso distinto pero relacionado: verificar si un proceso automatizado corrió todos los días que debía, entre una fecha de inicio y hoy — incluyendo explícitamente los días donde no hay registro de ejecución (que es justo lo que se necesita detectar).

DECLARE @FechaInicio DATE = '2025-01-01';

;WITH Calendar AS (
    SELECT CAST(@FechaInicio AS DATE) AS Fecha
    UNION ALL
    SELECT DATEADD(DAY, 1, Fecha)
    FROM Calendar
    WHERE Fecha < CAST(GETDATE() AS DATE)
)
SELECT 
    c.Fecha,
    l.Fecha_ejecucion,
    CASE 
        WHEN l.Fecha_ejecucion IS NULL THEN 'FALTA'
        ELSE 'OK'
    END AS Estado
FROM Calendar c
LEFT JOIN (
    SELECT CAST(Fecha_ejecucion AS DATE) AS Fecha_ejecucion
    FROM Proceso_Log
    WHERE proceso = 'Carga_Diaria'
) l
    ON l.Fecha_ejecucion = c.Fecha
OPTION (MAXRECURSION 366);

Cómo funciona el CTE recursivo

Un CTE recursivo (Common Table Expression) se define en dos partes unidas por UNION ALL: el ancla (el primer SELECT, que da el punto de partida — aquí, la fecha de inicio) y la parte recursiva (el segundo SELECT, que hace referencia a sí misma, sumando un día cada vez). SQL Server repite la parte recursiva una y otra vez, generando una fila nueva por cada iteración, hasta que la condición del WHERE deja de cumplirse (cuando Fecha alcanza el día de hoy). El resultado es una tabla virtual con una fila por cada día del rango, sin necesitar ninguna tabla física de calendario pre-creada.

OPTION (MAXRECURSION 366) es obligatorio para rangos largos: por defecto, SQL Server limita cualquier CTE recursivo a 100 iteraciones como medida de seguridad (para evitar loops infinitos accidentales) — 366 cubre incluso un año bisiesto completo. Si el rango a cubrir fuera mayor a un año, este número debe ajustarse en consecuencia.

El LEFT JOIN contra el log real de ejecuciones es lo que revela los huecos: como es un LEFT JOIN (no un INNER JOIN), todas las fechas del calendario aparecen en el resultado sin importar si hay o no un registro de ejecución correspondiente — cuando no lo hay, el campo Fecha_ejecucion queda en NULL, y el CASE lo marca explícitamente como 'FALTA'.

Nota sobre deadlocks al consultar tablas con carga masiva activa

Al ejecutar consultas de solo lectura (SELECT) contra tablas que en ese momento están recibiendo una carga masiva de datos (un INSERT o UPDATE grande), es posible toparse con un error de deadlock, donde SQL Server elige una de las dos transacciones en conflicto como «víctima» y la cancela para poder resolver el bloqueo mutuo.

Es importante entender que, si el SELECT es elegido como víctima, no significa que haya dañado ni afectado el proceso de carga masiva — SQL Server simplemente decide cuál de las dos operaciones en conflicto cancelar, generalmente priorizando la transacción más costosa de revertir. Lo más seguro en ese caso es simplemente esperar a que el proceso masivo termine, y volver a intentar la consulta después, en vez de forzar la ejecución repetidamente mientras la carga sigue en curso.

More posts