Update oldID for the records recursively
I asked a very similar question [here][1] but I need more advance query now. The situation is, we have about 20,000 records of customers. Customers can renew and we just create a new record for it. There is no exact tracing back to which record was actually renewed. Now we have added the `old ID` field and I want to populate it with the exact old record. Now if it is renewed one time, that has been taken care of my old question and I can do it. The problem is if a record was renewed 3 or more time, I have to find the exact logic how the oldest record hopped to the newest one. For this we do not have a set rule but generally I am following `customer start date` (and the ID itself throws some light which record came first) and for now will use `customer start date` to populate all the records. I have included a test case here

    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) 

    -- I want this result in the last column
    
    id   cust_no      meter_no   startdate      enddate        oldid           
    ---- ------------ ---------- -------------- -------------- -------         
    1    AA111222     1111       2008-01-01     2008-03-01     base                      
    2    AA111222     1111       2009-02-01     2009-05-01     3 
    3    AA111222     1111       2008-03-01     2008-12-01     1            
    4    AA111222     1111       2009-05-01     2009-07-01     2            
    5    AA111222     1111       2009-08-01     2009-11-01     4            
    6    AA111222     1111       2010-01-01     2010-04-01     5            
    7    AA111222     1111       2010-07-01     2011-07-01     6            
    8    AA111222     1111       2011-03-01     2011-07-01     7            
    9    AA111222     1111       2011-07-01     2012-07-01     8    

  

Note,different ways of doing it is appreciated so I can learn something as well. So far I have looked at CTE, Join, Cursor but it would take time for me to write it, if I can even do it.

  [1]: http://stackoverflow.com/questions/8994720/update-oldid-field-based-on-fields-in-the-same-table
Update oldID for the records recursively
===

I asked a very similar question [here][1] but I need more advance query now. The situation is, we have about 20,000 records of customers. Customers can renew and we just create a new record for it. There is no exact tracing back to which record was actually renewed. Now we have added the `old ID` field and I want to populate it with the exact old record. Now if it is renewed one time, that has been taken care of my old question and I can do it. The problem is if a record was renewed 3 or more time, I have to find the exact logic how the oldest record hopped to the newest one. For this we do not have a set rule but generally I am following `customer start date` (and the ID itself throws some light which record came first) and for now will use `customer start date` to populate all the records. I have included a test case here

    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) 

    -- I want this result in the last column
    
    id   cust_no      meter_no   startdate      enddate        oldid           
    ---- ------------ ---------- -------------- -------------- -------         
    1    AA111222     1111       2008-01-01     2008-03-01     base                      
    2    AA111222     1111       2009-02-01     2009-05-01     3 
    3    AA111222     1111       2008-03-01     2008-12-01     1            
    4    AA111222     1111       2009-05-01     2009-07-01     2            
    5    AA111222     1111       2009-08-01     2009-11-01     4            
    6    AA111222     1111       2010-01-01     2010-04-01     5            
    7    AA111222     1111       2010-07-01     2011-07-01     6            
    8    AA111222     1111       2011-03-01     2011-07-01     7            
    9    AA111222     1111       2011-07-01     2012-07-01     8    

  

Note,different ways of doing it is appreciated so I can learn something as well. So far I have looked at CTE, Join, Cursor but it would take time for me to write it, if I can even do it.

  [1]: http://stackoverflow.com/questions/8994720/update-oldid-field-based-on-fields-in-the-same-table