Andrei M Andrei M - 2 years ago 83
SQL Question

Convert a list of dates to date ranges in SQL Server

I have a query as following:

SELECT [Date] FROM [TableX] ORDER BY [Date]

The result is:


How can I get following pairs?

From To
2016-06-01 2016-06-03
2016-06-03 2016-06-10
2016-06-10 2016-06-11

Answer Source

A little tricky solution for SQL 2008.

declare @tbl table(dt datetime)
insert @tbl values

;with cte as (
select dt, ROW_NUMBER() over(order by dt) rn --add number
from @tbl
newTbl as (
select t1.dt start, t2.dt [end]
from cte t1 inner join cte t2 on t1.rn+1=t2.rn
select *
from newTbl

The result is what you wish.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download