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
   

 

---