fusionar fechas cuando las fechas siguientes se encuentren dentro de un intervalo de cero o un día
Quiero combinar múltiples registros en uno solo cuando la fecha de inicio sigue a la fecha de fin o cuando la fecha de inicio es igual a la fecha de fin en SQL Server, basado en el ID, y además obtener el MAX(ID2) dentro de ese grupo.
A continuación se muestra el ejemplo de entrada y salida. También se ha agregado el código SQL para la tabla de entrada:
create table #T (ID1 INT, ID2 INT, StartDate DATE, EndDate DATE)
insert into #T values
(100, 764286, '2019-05-01', '2019-05-31'),
(100, 764287, '2019-06-01', '2019-06-30'),
(100, 764288, '2019-07-10', '2019-07-31'),
(101, 764289, '2020-02-01', '2020-02-29'),
(101, 764290, '2020-02-29', '2020-03-31'),
(102, 764291, '2021-10-01', '2021-10-31'),
(102, 764292, '2021-11-01', '2021-11-30'),
(102, 764293, '2021-11-30', '2021-12-31'),
(103, 764294, '2022-01-01', '2022-01-31');
Aquí está el script que intenté, pero no está dando los resultados que espero para el ID 100, no debería combinar todos los registros relacionados con el ID 100:
select m.ID1,
NewID2 AS ID2,
m.StartDate,
lead(dateadd(day, -1, StartDate), 1, MaxEndDate) over (partition by ID1 order by StartDate) as EndDate
from (select *,
lag(StartDate) over (partition by ID1 order by StartDate) as S1,
lag(StartDate) over (partition by ID1 order by StartDate) as S2,
max(EndDate) over (partition by ID1) as MaxEndDate,
max(ID2) over (partition by ID1) as NewID2
from #T
) m
where S2 is null or S1 <> S2;
davy.ai
Para fusionar múltiples registros en un solo registro cuando la fecha de inicio sigue a la fecha de fin o cuando la fecha de inicio es igual a la fecha de fin en SQL Server basado en el ID, y para obtener el MAX(ID2) dentro de ese grupo, se puede utilizar el siguiente script:
El script primero crea una Expresión de Tabla Común (CTE) para asignar números de fila a cada registro basado en sus fechas de inicio para cada ID. Luego, crea otra CTE para encontrar los registros que tienen fechas de fin que siguen a las fechas de inicio del siguiente registro o que tienen fechas de inicio iguales a las fechas de fin del registro anterior. Lo hace uniéndose a la primera CTE consigo misma y comparando las fechas de fin/inicio para encontrar los registros coincidentes para cada grupo. También calcula el máximo ID2 dentro de cada grupo para usarlo como el nuevo ID2 para el registro fusionado.
La segunda CTE usa UNION ALL para combinar los registros coincidentes encontrados en el paso anterior con los registros que no formaron parte de ningún grupo. También calcula el máximo ID2 dentro de cada grupo para usarlo como el nuevo ID2 para el registro fusionado.
La tercera CTE agrupa los registros fusionados por su ID y el número de grupo (basado en el número de fila de la fecha de inicio menos el número de fila actual) y calcula la fecha de inicio mínima, la fecha de fin máxima y el ID2 máximo dentro de cada grupo.
Finalmente, el script selecciona las columnas de ID, nuevo ID2, fecha de inicio y fecha de fin de los resultados de CTE3 y ordena los resultados por el ID y la fecha de inicio.
Cabe destacar que este script asume que la columna ID2 es única para cada registro. Si ese no es el caso, es posible que se deba agregar una lógica adicional al script.