I have a column of a mostly continous unique dates in ascending order. Although the dates are mostly continuos, there are some gaps in the dates of less than 3 days, others have more than 3 days.
I need to create a table where each record has a start date and an end date of the range that includes a gap of 3 days or less. But a new record has to be generated if the gap is longer than 3 days.
so if dates are:
You can do something like this:
WITH CTE_Source AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY DT) RN FROM dbo.Table1 ) ,CTE_Recursion AS ( SELECT *, 1 AS Grp FROM CTE_Source WHERE RN = 1 UNION ALL SELECT src.*, CASE WHEN DATEADD(DD,3,rec.DT) < src.DT THEN rec.Grp + 1 ELSE Grp END AS Grp FROM CTE_Source src INNER JOIN CTE_Recursion rec ON src.RN = rec.RN +1 ) SELECT MIN(DT) AS StartDT, MAX(DT) AS EndDT FROM CTE_Recursion GROUP BY Grp
First CTE is just to assign continuous numbers for all rows in order to join them later. Then using recursive CTE you can join on each next row assigning groups if date difference is larger than 3 days. In the end just group by grouping column and select desired results.