update privious date

Posted on 2007-09-30
Last Modified: 2010-03-19
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?
Question by:jung1975
    LVL 19

    Expert Comment

    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
    LVL 19

    Accepted Solution

    eg to exec that proc:
    exec spSetPrevious '20070630'

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now