Andrei M Andrei M - 6 months ago 8
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:

2016-06-01
2016-06-03
2016-06-10
2016-06-11


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

A little tricky solution for SQL 2008.

declare @tbl table(dt datetime)
insert @tbl values
('2016-06-01'),
('2016-06-03'),
('2016-06-10'),
('2016-06-11')

;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.