Convert list of dates in a date range in SQL Server

Question

Convert list of dates in a date range in SQL Server

For ex:

enter image description here

• Alexei: How do you obtain those intervals? I see 7 records on the left and only 6 values on the right.

Answer 1

It's classic "islands" problem.

Here is "classic" solution:

declare @t table (dt date);
insert into @t(dt) values 
 ('20180202'), 
 ('20180203'), 
 ('20180204'), 
 ('20180205'), 
 ('20180209'), 
 ('20180212'), 
 ('20180213');
with c as 
( 
 select dt, dateadd(day, -1 * dense_rank() over(order by dt), dt) as grp 
 from @t 
) 
select min(dt) as start_range, max(dt) as end_range 
from c 
group by grp;