question

gauravdudeja avatar image
gauravdudeja asked

Convert list of dates in a date range

How to convert list of dates in a date range for ex Dates 2/2/2018 2/3/2018 2/4/2018 2/5/2018 2/9/2018 2/12/2018 2/13/2018 to DateFrom DateTo 2/2/2018 2/5/2018 2/9/2018 2/9/2018 2/12/2018 2/13/2018
datedatesdate-range
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
This type of problem is the classic Gaps and Islands problem. One way to find the Islands (groups of consecutive values) would be this: declare @dates table(d date); insert @dates (d) values ('2/2/2018 '), ('2/3/2018 '), ('2/4/2018 '), ('2/5/2018 '), ('2/9/2018 '), ('2/12/2018'), ('2/13/2018'); with cte as (select case when lag(d,1,d) over(order by d)=dateadd(day,-1,d) then 0 else 1 end as firstinisland, d from @dates ),islands as ( select sum(firstinisland) over(order by d rows between unbounded preceding and current row) as islandid, d from cte ) select min(d) as islandstart,max(d) as islandend from islands group by islandid; There are many more efficient ways to solve the problem Google on Gaps and Islands and T-SQL and you will find many more suggestions. But I think the above is understandable enough to start working with.
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Another very efficient but simple technique is available in this wonderful [SQL Spackle][1] by @Jeff Moden. Although, an answer was already posted, but the only reason I am mentioning it is for the detailed explanation and for the easiness to understand the code bit by bit. set dateformat MDY go declare @dates table ( DateCol date ) insert @dates values( '2/2/2018'), ('2/3/2018'), ('2/4/2018'), ('2/5/2018'), ('2/9/2018'), ('2/12/2018'), ('2/13/2018'), ('2/13/2018'); with cte as ( select DATEADD(DAY, -ROW_NUMBER() over(order by DateCol), DateCol) DateGroup, DateCol from @dates group by DateCol ) select MIN(datecol) MinDate, MAX(DateCol) MaxDate from cte group by DateGroup; [1]: http://www.sqlservercentral.com/articles/T-SQL/71550/
1 Like 1 ·
I was just about to post my solution - with the same approach as this. :-)
0 Likes 0 ·
It's an interesting problem and it's really nice to have window functions in this case. I guess a count rather than a SUM would be a tiny bit more efficient than my solution.
0 Likes 0 ·
@Magnus Ahlkvist. +1 for using the new window functions. The only concern I have is that if there would be any duplicate date, the code will not work correctly. So a CTE with distinct dates should be there for the code to be fool proof. As far as using COUNT instead of SUM is concerned, I believe that would give wrong results.
0 Likes 0 ·
That's a very elegant solution Usman Butt. And doesn't require SQL Server 2012 or later (since 2008 and 2008R2 are still in extended support that's a really good thing).
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.