T-SQL– Find Gap and Island for Date Range


Here is a solution for gaps and island from Alejandro Mesa:

http://blogs.msdn.com/b/samlester/archive/2012/09/04/tsql-solve-it-your-way-gaps-and-islands-with-a-twist.aspx

Another one:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d3d38967-2802-43aa-ae2f-1f58b0b25ec1/min-and-max-date-of-blocks-of-subsequent-days?forum=transactsql


  
  SET NOCOUNT ON;
USE tempdb;
GO
create table #test (Item char(1), [Date] Date)
GO
Insert into #test 
VALUES
('A', '8/21/2014'),('A', '8/22/2014'),('A', '8/25/2014'),
('B', '09/12/2014'),('B', '09/13/2014'),('B', '09/14/2014'),
('A', '10/11/2014'),('A', '10/12/2014'),('A', '10/13/2014') 
GO
;with mycte as (
SELECT
    *,
    DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY Item ORDER BY [Date]), [Date]) AS grp
FROM
    #test
)
select Item, min([Date]) as[From],max([Date]) as [To] from mycte
group by Item, grp
Order by Item, grp
GO
drop table #test
GO
   

 
Advertisements

One Comment on “T-SQL– Find Gap and Island for Date Range”

  1. Gal Buki says:

    Thanks for the SQL query. This was exactly what I was looking for.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s