Source Result set
---------------

Source Result set

Destination result set
-----------------------

Destination Result set

CREATE TABLE #SourceData (id int,startdate datetime,endate datetime, statu tinyint)
INSERT INTO  #SourceData values (1,'2013-04-02','2013-04-03',1)
INSERT INTO  #SourceData values (2,'2013-04-04','2013-04-05',1)
INSERT INTO  #SourceData values (3,'2013-04-06','2013-04-07',0)
INSERT INTO  #SourceData values (4,'2013-04-08','2013-04-09',0)
INSERT INTO  #SourceData values (5,'2013-04-10','2013-04-11',0)
INSERT INTO  #SourceData values (6,'2013-04-12','2013-04-13',1)
INSERT INTO  #SourceData values (7,'2013-04-14','2013-04-15',1)
INSERT INTO  #SourceData values (8,'2013-04-16','2013-04-17',0)
INSERT INTO  #SourceData values (9,'2013-04-18','2013-04-19',1)

SELECT  s.*,d.statu  as pre
  INTO #temp
  FROM #SourceData s
  LEFT join #SourceData d
    ON s.id=d.id+1

declare @id as int=0
update #temp
    set @id=case when statu<> isnull(pre,7) then @id+1 else @id end
      , pre= @id



select  statu,COUNT(pre) cnt,MIN(startdate) startdate,MAX(endate)
enddate from #temp
group by pre,statu
order by 3