HEre's something to start with:Declare @d table (member_id int, start_date datetime, end_date datetime, touch_date datetime, insu_plan int, row_id int)insert into @d select 11110002, '2006-01-01', NULL, '2007-06-15', 1235, 1 union allselect 11110002, '2006-01-01', NULL, '2007-07-24', 1240, 2 union allselect 11110003, '2007-01-01', NULL, '2007-06-15', 1235, 3 union allselect 11110003, '2007-01-01', NULL, '2007-07-24', 1240, 4select * from @d dWhere D.row_id in (Select Max(row_Id) From @d D2 Group by Member_id )And D.end_date is nullupdate dset d.end_date = '2079-06-30'from @d DWhere D.row_id in ((Select Max(row_Id) From @d D2 Group by Member_id ) )and d.end_date is nullselect * from @d dupdate dset d.end_date = d.start_date - 1from @d DWhere D.row_id in ((Select Max(row_Id) From @d D2 Where D2.end_date is null Group by Member_id ) )and d.end_date is nullselect * from @d d
You might have to tweak this a little. You only provided us 2 rows of data which is too little to cover any scenarios.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/