Detect consecutive dates ranges using SQL

by

No joins or recursive CTEs needed. The standard gaps-and-island solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group.

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)

More Related Contents:

  1. sql group by only rows which are in sequence
  2. I dont know sql syntax for my table [closed]
  3. Unable to Subtract Dates SQL Server
  4. CROSS JOIN vs INNER JOIN in SQL
  5. How to cast the DateTime to Time
  6. How can I schedule a job to run a SQL query daily?
  7. Reset identity seed after deleting records in SQL Server
  8. How to avoid the “divide by zero” error in SQL?
  9. Multiple INSERT statements vs. single INSERT with multiple VALUES
  10. Real life example, when to use OUTER / CROSS APPLY in SQL
  11. Common Table Expression, why semicolon?
  12. T-SQL split string based on delimiter
  13. SQL Server 2008: how do I grant privileges to a username?
  14. Splitting delimited values in a SQL column into multiple rows
  15. How do I find duplicates across multiple columns?
  16. Split values over multiple rows [duplicate]
  17. How to write UPDATE SQL with Table alias in SQL Server 2008?
  18. SQL Server 2008- Get table constraints
  19. Restrict an SQL Server connection to a specific IP address
  20. How to do an inner join on row number in sql server
  21. Why are logical reads for windowed aggregate functions so high?
  22. Stored procedure that exports data into csv files only exports to one file
  23. SQL Server: should I use information_schema tables over sys tables?
  24. SQL count consecutive days
  25. TSQL – How to use GO inside of a BEGIN .. END block?
  26. SQL take just the numeric values from a varchar
  27. Backup a single table with its data from a database in sql server 2008
  28. COALESCE Function in TSQL
  29. Try_Convert for SQL Server 2008 R2
  30. Sql query for tree table

Leave a Comment