Detecta intervalos de dates consecutivos usando SQL

Deseo completar el object de calendar que requiere información de date de inicio y finalización. Tengo una columna que contiene una secuencia de dates. Algunas de las dates son consecutivas (tienen una diferencia de un día) y otras no.

InfoDate 2013-12-04 consecutive date [StartDate] 2013-12-05 consecutive date 2013-12-06 consecutive date [EndDate] 2013-12-09 [startDate] 2013-12-10 [EndDate] 2014-01-01 [startDate] 2014-01-02 2014-01-03 [EndDate] 2014-01-06 [startDate] 2014-01-07 [EndDate] 2014-01-29 [startDate] 2014-01-30 2014-01-31 [EndDate] 2014-02-03 [startDate] 2014-02-04 [EndDate] 

Quiero elegir la date de inicio y finalización de cada intervalo de dates consecutivas (la primera y la última en el bloque).

 StartDate EndDate 2013-12-04 2013-12-06 2013-12-09 2013-12-10 2014-01-01 2014-01-03 2014-01-06 2014-01-07 2014-01-29 2014-01-31 2014-02-03 2014-02-04 

Quiero resolver el problema usando solo SQL.

No se necesitan combinaciones o CTE recursivos. La solución estándar de lagunas e islas es agrupar por (valor less row_number), ya que eso es invariante dentro de una secuencia consecutiva. Las dates de inicio y finalización son solo MIN () y MAX () del grupo.

 WITH t AS ( SELECT InfoDate d,ROW_NUMBER() OVER(ORDER BY InfoDate) i FROM @d GROUP BY InfoDate ) SELECT MIN(d),MAX(d) FROM t GROUP BY DATEDIFF(day,i,d) 

Aqui tienes..

 ;WITH CTEDATES AS ( SELECT ROW_NUMBER() OVER (ORDER BY Infodate asc ) AS ROWNUMBER,infodate FROM YourTableName ), CTEDATES1 AS ( SELECT ROWNUMBER, infodate, 1 as groupid FROM CTEDATES WHERE ROWNUMBER=1 UNION ALL SELECT a.ROWNUMBER, a.infodate,case datediff(d, b.infodate,a.infodate) when 1 then b.groupid else b.groupid+1 end as gap FROM CTEDATES A INNER JOIN CTEDATES1 B ON A.ROWNUMBER-1 = B.ROWNUMBER ) select min(mydate) as startdate, max(infodate) as enddate from CTEDATES1 group by groupid 

por favor no olvide marcarlo como respuesta, si esto responde su pregunta.

He insertado estos valores en una tabla llamada #consec y luego preforemed lo siguiente:

 select t1.* ,t2.infodate as binfod into #temp1 from #consec t1 left join #consec t2 on dateadd(DAY,1,t1.infodate)=t2.infodate select t1.* ,t2.infodate as binfod into #temp2 from #consec t1 left join #consec t2 on dateadd(DAY,1,t2.infodate)=t1.infodate ;with cte as( select infodate, ROW_NUMBER() over(order by infodate asc) as seq from #temp1 where binfod is null ), cte2 as( select infodate, ROW_NUMBER() over(order by infodate asc) as seq from #temp2 where binfod is null ) select t2.infodate as [start_date] ,t1.infodate as [end_date] from cte t1 left join cte2 t2 on t1.seq=t2.seq 

Siempre que sus períodos de dates no se superpongan, eso debería hacer el trabajo por usted.

Aquí está mi muestra con datos de testing:

 --requinetworking output -- 01 - 03 -- 08 - 09 -- 12 - 14 DECLARE @maxRN int; WITH #tmp AS ( SELECT CAST('2013-01-01' AS date) DT UNION ALL SELECT CAST('2013-01-02' AS date) UNION ALL SELECT CAST('2013-01-03' AS date) UNION ALL SELECT CAST('2013-01-05' AS date) UNION ALL SELECT CAST('2013-01-08' AS date) UNION ALL SELECT CAST('2013-01-09' AS date) UNION ALL SELECT CAST('2013-01-12' AS date) UNION ALL SELECT CAST('2013-01-13' AS date) UNION ALL SELECT CAST('2013-01-14' AS date) ), #numbenetworking AS ( SELECT 0 RN, CAST('1900-01-01' AS date) DT UNION ALL SELECT ROW_NUMBER() OVER (ORDER BY DT) RN, DT FROM #tmp ) SELECT * INTO #tmpTable FROM #numbenetworking; SELECT @maxRN = MAX(RN) FROM #tmpTable; INSERT INTO #tmpTable SELECT @maxRN + 1, CAST('2100-01-01' AS date); WITH #paird AS ( SELECT ROW_NUMBER() OVER(ORDER BY TStart.DT) RN, TStart.DT DTS, TEnd.DT DTE FROM #tmpTable TStart INNER JOIN #tmpTable TEnd ON TStart.RN = TEnd.RN - 1 AND DATEDIFF(dd,TStart.DT,TEnd.DT) > 1 ) SELECT TS.DTE, TE.DTs FROM #paird TS INNER JOIN #paird TE ON TS.RN = TE.RN -1 AND TS.DTE <> TE.DTs -- you could remove this filter if you want to have start and end on the same date DROP TABLE #tmpTable 

Reemplace los datos de #tmp con su tabla actual.