- Stack Exchange Data Explorer

declare @T table ( [Date] datetime, Code char(1) ) inse...

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

-- Enter Query Title
-- Enter Query Description

declare @T table
(
  [Date] datetime,
  Code char(1)
)

insert into @T values
('1/1/2011','A'),  
('1/2/2011','A'),  
('1/3/2011','A'),  
('1/1/2011','B'),  
('1/2/2011','B'),
('2/1/2011','A'),
('2/2/2011','A'),
('2/27/2011','A'),
('2/28/2011','A'),
('3/1/2011','A'),
('3/2/2011','A'),
('3/3/2011','A'),
('3/4/2011','A')

;with C as
(
  select *,
         datediff(day, 0, [Date]) - row_number() over(partition by Code 
                                                      order by [Date]) as rn
  from @T
)  
select min([Date]) as StartDate,
       max([Date]) as EndDate,
       Code
from C
group by Code, rn

created sep 9 11