Q&A for professional and enthusiast programmers
create table #customer ( id int not null primary key identity, cust_no varchar(12), meter_no varchar(10), startdate smalldatetime, enddate smalldatetime, oldid int null ) insert into #customer values('AA111222','1111','2008-01-01', '2008-03-01',null) insert into #customer values('AA111222','1111','2009-02-01', '2009-05-01',null) insert into #customer values('AA111222','1111','2008-03-01', '2008-12-01',null) insert into #customer values('AA111222','1111','2009-05-01', '2009-07-01',null) insert into #customer values('AA111222','1111','2009-08-01', '2009-11-01',null) insert into #customer values('AA111222','1111','2010-01-01', '2010-04-01',null) insert into #customer values('AA111222','1111','2010-07-01', '2011-07-01',null) insert into #customer values('AA111222','1111','2011-03-01', '2011-07-01',null) insert into #customer values('AA111222','1111','2011-07-01', '2012-07-01',null) update #customer set oldid = 0 where startdate in (select min(startdate) from #customer) update #customer set oldid = a.oldid+1 from #customer inner join (select oldid, enddate from #customer where oldid = (select max(oldid) from #customer)) a on a.enddate = startdate declare @test int select @test = 0 while (select count(*) from #customer where oldid is null) > 0 begin select @test = @test + 1 update #customer set oldid = a.oldid+1 from #customer inner join (select oldid, enddate from #customer where oldid = (select max(oldid) from #customer)) a on a.enddate = startdate if(@test > 10) break end select * from #customer drop table #customer