Detectar periodos consecutivos en SQL Server

Una tabla de actividad guarda observaciones individuales. La pregunta de negocio suele pedir cuántos días estuvo activo un cliente sin interrupción. Antes de elegir una función de ventana hay que definir continuidad: dos filas vecinas no son necesariamente días consecutivos.

Normalizar la unidad observada

El ejemplo cuenta una observación por cliente y fecha. Varios eventos del mismo día cuentan una vez. Sin normalización, las fechas duplicadas pueden distorsionar grupos y longitud de rachas. La entrada incluye deliberadamente un duplicado, un día ausente y otro cliente.

CREATE TABLE #Activity(CustomerId int, ActivityDate date);
INSERT #Activity VALUES
(1,'20230101'),(1,'20230102'),(1,'20230102'),
(1,'20230104'),(1,'20230105'),(2,'20230102');
;WITH days AS
(SELECT DISTINCT CustomerId, ActivityDate FROM #Activity),
previous AS
(SELECT *, LAG(ActivityDate) OVER
 (PARTITION BY CustomerId ORDER BY ActivityDate) AS PrevDate FROM days),
flags AS
(SELECT *, CASE WHEN DATEDIFF(day, PrevDate, ActivityDate)=1
 THEN 0 ELSE 1 END AS NewIsland FROM previous),
groups AS
(SELECT *, SUM(NewIsland) OVER
 (PARTITION BY CustomerId ORDER BY ActivityDate
  ROWS UNBOUNDED PRECEDING) AS IslandId FROM flags)
SELECT CustomerId, MIN(ActivityDate) AS StartDate,
 MAX(ActivityDate) AS EndDate, COUNT(*) AS ActiveDays
FROM groups GROUP BY CustomerId, IslandId
ORDER BY CustomerId, StartDate;

La primera CTE elimina pares repetidos. LAG obtiene la fecha distinta anterior dentro del cliente. Un indicador marca la primera fila o un salto diferente de un día. La suma acumulada convierte indicadores en grupos, y la agregación calcula inicio, fin y días activos.

Para el cliente 1 deben aparecer enero 1 a enero 2 y enero 4 a enero 5. El cliente 2 tiene una isla independiente de un día. COUNT(*) ahora representa días porque esa granularidad se estableció al principio; sin deduplicar representaría eventos. El marco ROWS explícito aclara la acumulación.

Precisar el calendario de negocio

Días naturales, días laborables y observaciones consecutivas son conceptos diferentes. Viernes y lunes interrumpen una racha natural, pero pueden continuar una laboral. En ese caso utilice una tabla calendario mantenida con un número consecutivo de día laborable y compare ese número en lugar de sumar un día natural.

Si la fuente contiene marcas temporales, elija la zona del informe antes de extraer fechas. Convertir UTC puede mover un evento al día local anterior o siguiente. Los cambios de horario alteran horas transcurridas; veinticuatro horas no equivalen siempre a fechas locales consecutivas. Conserve la marca original para auditoría.

El filtro temporal introduce otra frontera. Una racha visible el primero de febrero puede haber empezado en enero. Decida entre mostrar un periodo recortado o la racha completa. Encontrar el inicio real exige más historial o un estado previo mantenido, no simplemente cambiar la etiqueta presentada.

Distinguir intervalos superpuestos

Fusionar intervalos requiere otra comparación. Considere 1 a 10, 2 a 3 y 9 a 12. Comparar el tercer inicio solo con el final inmediatamente anterior inventa un hueco. Debe utilizar el máximo acumulado de todos los finales anteriores y definir si los extremos son inclusivos o exclusivos.

Para días de actividad, un índice por cliente y fecha puede ayudar con orden y deduplicación. El plan real determina si aún necesita ordenar. En historiales grandes, revise filas procesadas y derrames de memoria. Filtre pronto los clientes necesarios sin perder el historial requerido por las rachas completas.

Pruebe un día, duplicados, varios clientes, un hueco y una racha que atraviese el inicio del informe. Añada eventos tardíos: insertar un día faltante puede unir dos islas existentes. Los identificadores calculados no son claves de negocio permanentes automáticamente. Compruebe además si los informes almacenados deben recalcularse o conservar su fotografía original. La corrección depende de esas reglas y pruebas, no únicamente de una consulta elegante.

Referencias técnicas: Microsoft Learn: LAG · Microsoft Learn: OVER.