Posteriormente a investigar con expertos en la materia, programadores de diversas ramas y maestros dimos con la solución a la pregunta y la dejamos plasmada en este post.
Solución:
Aquí hay una consulta que tiene el mejor rendimiento de todos los envíos hasta ahora, con solo dos accesos a la tabla en el plan de ejecución (en lugar de tres o más). Por supuesto, todas las consultas son ayudadas por índices. Tenga en cuenta que el plan de ejecución califica esta consulta como más cara, pero las lecturas y la CPU reales son significativamente mejores. Los costos estimados en los planes de ejecución no son los mismos que el desempeño real.
WITH Grps AS (
SELECT
(Row_Number() OVER (ORDER BY P1.StartDate) - 1) / 2 Grp,
P1.StartDate,
P1.EndDate
FROM
Periods P1
CROSS JOIN (SELECT -1 UNION ALL SELECT 1) D (Dir)
LEFT JOIN Periods P2 ON
DateAdd(Day, D.Dir, P1.StartDate) = P2.EndDate
OR DateAdd(Day, D.Dir, P1.EndDate) = P2.StartDate
WHERE
(Dir = -1 AND P2.EndDate IS NULL)
OR (Dir = 1 AND P2.StartDate IS NULL)
)
SELECT
Min(StartDate) StartDate,
Max(EndDate) EndDate
FROM Grps
GROUP BY Grp;
Una cosa más que creo que vale la pena mencionar es que consultar su tabla de período de fecha en la mayoría de los casos sería más simple y de mejor rendimiento si usara fechas de finalización exclusivas (también conocidas como fechas de finalización “abiertas”) en lugar de fechas cerradas:
StartDate | EndDate | EndDate
(Inclusive) | (Inclusive) | (Exclusive)
---------------------------------------
1982.03.02 | 1982.09.30 | 1982.10.01
1982.10.01 | 1985.01.17 | 1985.01.18
El uso de fechas de finalización exclusivas es (en mi opinión) la mejor práctica la mayor parte del tiempo porque le permite cambiar el tipo de datos de la columna de fecha o cambiar la resolución de la fecha, sin afectar ninguna consulta, código u otra lógica. Por ejemplo, si sus fechas debían estar redondeadas a las 12 horas más cercanas en lugar de 24 horas, tendría que trabajar mucho para lograrlo, mientras que si usara fechas de finalización exclusivas, ¡ni una sola cosa tendría que cambiar!
Si estuviera utilizando fechas de finalización exclusivas, mi consulta se vería así:
WITH Grps AS (
SELECT
(Row_Number() OVER (ORDER BY P1.StartDate) - 1) / 2 Grp,
P1.StartDate,
P1.EndDate
FROM
Periods P1
CROSS JOIN (SELECT 1 UNION ALL SELECT 2) X (Which)
LEFT JOIN Periods P2 ON
(X.Which = 1 AND P1.StartDate = P2.EndDate)
OR (X.Which = 2 AND P1.EndDate = P2.StartDate)
WHERE
P2.EndDate IS NULL
OR P2.StartDate IS NULL
)
SELECT
Min(StartDate) StartDate,
Max(EndDate) EndDate
FROM Grps
GROUP BY Grp;
Observe que ahora no hay DateAdd o DateDiff, con valores codificados de “1 día” que tendrían que cambiar si, por ejemplo, cambiara a períodos de 12 horas.
Actualizar
Aquí hay una consulta actualizada que incorpora las cosas que he aprendido en los últimos casi 5 años. Esta consulta ahora no tiene combinaciones en absoluto, y aunque tiene 3 operaciones de ordenación que podrían ser problemas de rendimiento, creo que esta consulta competirá razonablemente bien y, en ausencia de índices, probablemente superará a todas las demás.
WITH Groups AS (
SELECT Grp = Row_Number() OVER (ORDER BY StartDate) / 2, *
FROM
#Periods
(VALUES (0), (0)) X (Dup)
), Ranges AS (
SELECT StartDate = Max(StartDate), EndDate = Min(EndDate)
FROM Groups
GROUP BY Grp
HAVING Max(StartDate) <> DateAdd(day, 1, Min(EndDate))
), ReGroups AS (
SELECT
Grp = Row_Number() OVER (ORDER BY StartDate) / 2,
StartDate,
EndDate
FROM
Ranges
CROSS JOIN (VALUES (0), (0)) X (Dup)
)
SELECT
StartDate = Min(StartDate),
EndDate = Max(EndDate)
FROM ReGroups
GROUP BY Grp
HAVING Count(*) = 2
;
Y aquí hay otra versión que usa funciones de ventanas (algo parecido a lo que simula la consulta anterior):
WITH LeadLag AS (
SELECT
PrevEndDate = Coalesce(Lag(EndDate) OVER (ORDER BY StartDate), '00010101'),
NextStartDate = Coalesce(Lead(StartDate) OVER (ORDER BY StartDate), '99991231'),
*
FROM #Periods
), Dates AS (
SELECT
X.*
FROM
LeadLag
CROSS APPLY (
SELECT
StartDate = CASE WHEN DateAdd(day, 1, PrevEndDate) <> StartDate THEN StartDate ELSE NULL END,
EndDate = CASE WHEN DateAdd(day, 1, EndDate) <> NextStartDate THEN EndDate ELSE NULL END
) X
WHERE
X.StartDate IS NOT NULL
OR X.EndDate IS NOT NULL
), Final AS (
SELECT
StartDate,
EndDate = Min(EndDate) OVER (ORDER BY EndDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM Dates
)
SELECT *
FROM Final
WHERE StartDate IS NOT NULL
;
Me toma más tiempo configurar los datos de muestra que escribir la consulta; sería mejor si publicara preguntas que incluyan CREATE TABLE y INSERT/SELECT declaraciones. No sé cómo se llama tu tabla, he llamado a la mía Periodos:
create table Periods (
StartDate date not null,
EndDate date not null
)
go
insert into Periods(StartDate,EndDate)
select '19820302','19820930' union all
select '19821001','19850117' union all
select '19850626','19850726' union all
select '19850730','19911231' union all
select '19920101','19951231' union all
select '19960101','20040531' union all
select '20040605','20060131' union all
select '20060201','20110520'
go
; with MergedPeriods as (
Select p1.StartDate, p1.EndDate
from
Periods p1
left join
Periods p2
on
p1.StartDate = DATEADD(day,1,p2.EndDate)
where
p2.StartDate is null
union all
select p1.StartDate,p2.EndDate
from
MergedPeriods p1
inner join
Periods p2
on
p1.EndDate = DATEADD(day,-1,p2.StartDate)
)
select StartDate,MAX(EndDate) as EndDate
from MergedPeriods group by StartDate
Resultado:
StartDate EndDate
1982-03-02 1985-01-17
1985-06-26 1985-07-26
1985-07-30 2004-05-31
2004-06-05 2011-05-20
Agradecemos que quieras animar nuestro trabajo ejecutando un comentario o dejando una valoración te estamos eternamente agradecidos.