update privious date

I have a table like below

Id          event       start date    end date
1001           1          05/01/07      06/13/07
1001         2           07/01/07          09/11/07
1001        3           09/20/07        
1002          1        04/31/07             05/01/07
1002         2        06/12/07

I have a new record coming in and  I would like to update the end  date of privious event to the  date of new record if the start date of new record is between the  events..

For example..
New record will look like:
ID   date
1001  06/30/07

the date of this record is between 1 event and 2 event, so you have to update the end date of  privious event ( in this case, 1event)  to  new date ( 06/30/07)
Id          event       start date    end date
1001           1          05/01/07      06/30/07
1001         2           07/01/07         09/11/07
1001        3           09/20/07        
1002          1        04/31/07                   05/01/07
1002         2        06/12/07

how can i do this using a set based solution?
Who is Participating?
frankyteeConnect With a Mentor Commented:
eg to exec that proc:
exec spSetPrevious '20070630'
create a sp like below and parse your date parameter prior to inserting the new record

create procedure spSetPrevious (@ipDate smalldatetime)

update mytable
set [end date]= @ipDate
where [start date] in (select max([start date]) from mytable where [start date] < @ipDate)

after exec then insert your new record
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.