create table #a (ID int,Year int)

insert into #a values(1,1993)

insert into #a values(1,1994)

insert into #a values(1,1995)

insert into #a values(1,2001)

insert into #a values(1,2002)

insert into #a values(2,1995)

insert into #a values(2,1996)

insert into #a values(2,2000)

insert into #a values(2,2001)

insert into #a values(2,2002)

insert into #a values(2,2003)

create table #b (ID int,Year int,Flag int)

insert into #b select ID,Year,0 from #a

update b set Flag=1 from #b b left outer join #b x on x.ID = b.ID and x.Year = (b.Year - 1) where x.ID IS NULL

update b set Flag=2 from #b b left outer join #b x on x.ID = b.ID and x.Year = (b.Year + 1) where x.ID IS NULL

select b.ID,max((x.Year - b.Year) + 1) as 'ct' from #b b inner join #b x on x.ID = b.ID and x.Year = (select min(y.Year) from #b y where y.ID = b.ID and y.Year > b.Year and y.Flag=2) where b.Flag = 1 group by b.ID