operaciones - Detectar rangos de fechas consecutivas utilizando SQL

operaciones con fechas sql

Question

Quiero rellenar el objeto de calendario que requiere información de fecha de inicio y finalización. Tengo una columna que contiene una secuencia de fechas. Algunas de las fechas 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 fecha de inicio y finalización de cada intervalo de fechas 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.

Answer 1

Aquí está mi muestra con datos de prueba:

--required 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)
),
#numbered 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 #numbered;
SELECT @maxRN = MAX(RN) FROM #tmpTable;
INSERT INTO #tmpTable
SELECT @maxRN + 1, CAST(''2100-01-01'' AS date);
WITH #paired 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 #paired TS
INNER JOIN #paired 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 #tmp con su tabla real.

Answer 2

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 a su pregunta.

Answer 3

He insertado estos valores en una tabla llamada #consec y luego perforé 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 

Mientras sus períodos de fechas no se superpongan, debe hacer el trabajo por usted.

Answer 4

No se requieren uniones ni CTE recursivas. La solución estándar de lagunas e islas es agrupar por (valor menos número de fila), ya que es invariante dentro de una secuencia consecutiva. Las fechas de inicio y finalización son solo las 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)