• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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?
0
jung1975
Asked:
jung1975
  • 2
1 Solution
 
frankyteeCommented:
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
0
 
frankyteeCommented:
eg to exec that proc:
exec spSetPrevious '20070630'
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now