Link to home
Create AccountLog in
Avatar of jung1975
jung1975

asked on

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?
Avatar of frankytee
frankytee
Flag of Australia image

create a sp like below and parse your date parameter prior to inserting the new record

create procedure spSetPrevious (@ipDate smalldatetime)
as
begin

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

after exec then insert your new record
ASKER CERTIFIED SOLUTION
Avatar of frankytee
frankytee
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account