Cómo agrupar varias filas en una si cumplen una condición que involucra a todas las filas.
Tengo una tabla con nombres y datos de visitas como sigue:
Nombre | Fecha de visita | Fecha de vencimiento de licencia |
---|---|---|
John | 1/1/2020 | 3/30/2020 |
John | 2/1/2020 | 5/2/2020 |
John | 6/1/2020 | 9/30/2020 |
James | 3/15/2020 | 6/14/2020 |
Para cada nombre en la tabla, quiero resumir las fechas de visita en una sola (la primera) si las visitas están dentro de los 60 días una de la otra y utilizar la última fecha de vencimiento de licencia como la nueva fecha de vencimiento. Si la licencia de visita se emitió más de 60 días después de la última visita, quiero que comience un nuevo registro para que los resultados sean los siguientes:
Nombre | Fecha de visita | Fecha de vencimiento de licencia |
---|---|---|
John | 1/1/2020 | 5/2/2020 |
John | 6/1/2020 | 9/30/2020 |
James | 3/15/2020 | 6/14/2020 |
No pude encontrar la solución para esto.
davy.ai
Para lograr el resultado deseado, podemos utilizar el enfoque gaps-and-islands en T-SQL. Primero, necesitamos identificar las islas de visitas consecutivas para cada nombre con la misma fecha de expiración. Entonces, podemos seleccionar la primera visita en cada isla y la última fecha de expiración como el nuevo registro. Aquí está el código T-SQL que implementa esta lógica:
En la expresión de tabla común (CTE)
cte
, calculamos el número de grupo para cada visita restando el número de días entre la visita actual y la fecha de expiración previa de su número de fila dentro de la partición del nombre. Esto nos da un número único para cada isla de visitas consecutivas que tienen la misma fecha de expiración.En la consulta externa, agrupamos por nombre y número de grupo y seleccionamos la fecha mínima de visita y la fecha máxima de expiración para cada grupo. Esto nos da los registros consolidados como se desea.
Tenga en cuenta que esta solución supone que las visitas para cada nombre están ordenadas por fecha de visita en orden ascendente. Si no lo están, puede agregar una cláusula
ORDER BY Visitlicensedate
a la consulta externa.