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
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.
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/
I was just about to post my solution - with the same approach as this. :-)
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.
@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.
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).
Yes, indeed it is a very simple and elegant solution and should perform much better. As far as your suggestion for using COUNT instead of SUM in your solution, the following may work ;with DistinctDates as (select distinct DateCol d from @dates ), cte as (select case when lag(d,1,d) over(order by d)=dateadd(day,-1,d) then null else 1 end as firstinisland, d from DistinctDates ),islands as ( select count(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;
17 People are following this question.
