最大连续出现的次数

 with CET_Table as  
(
select 'A' name,1 as [month],0 as value
union all select 'A',2,1
union all select 'A',3,0
union all select 'A',4,0  
union all select 'A',5,1
union all select 'A',6,0  
union all select 'A',7,0  
union all select 'A',8,0  
union all select 'A',9,0  
union all select 'A',10,0  
union all select 'A',11,1  
union all select 'A',12,1   
union all select 'B',1,1
union all select 'B',2,0
union all select 'B',3,0
union all select 'B',4,1
union all select 'B',5,1
union all select 'B',6,1
union all select 'B',7,1
union all select 'B',8,1
union all select 'B',9,0
union all select 'B',10,0
union all select 'B',11,0
union all select 'B',12,1
)
--select * into ta from CET_Table ---根据CET快速创建一个表
,A as(
select * ,number = (select COUNT(1) from CET_Table B where B.name = A.name and B.value = A.value and B.month<=A.month
and B.month>=(select isnull(max(c.month),1) from CET_Table C where C.name = A.name and C.value <> A.value and C.month<=A.month))
from CET_Table A)
,B as(
select name,max(number)'0',0 as'1' from a   where  value=0  group by name
union all
select name,0 as '-',max(number) as'-' from a   where  value=1  group by name
) 
select name ,SUM([0])max_rest_time,SUM([1])max_working_time from b  group by name

---明细

--select * ,number = (select COUNT(1) from CET_Table B where B.name = A.name and B.value = A.value and B.month<=A.month
--and B.month>=(select isnull(max(c.month),1) from CET_Table C where C.name = A.name and C.value <> A.value and C.month<=A.month))
--from CET_Table A