Results 1 to 2 of 2

Thread: TSQL Query

  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Unanswered: TSQL Query

    I have a table with column values as below -

    ID StartDate
    1 Dec 30 2008 2:47PM
    2 Dec 30 2008 2:49PM
    3 Dec 30 2008 2:49PM
    4 Dec 30 2008 2:53PM
    5 Dec 30 2008 2:58PM
    6 Dec 30 2008 2:49PM
    7 Dec 30 2008 2:49PM
    8 Dec 31 2008 3:55PM

    What I'm trying to do is to have a script to update some of the incorrect dates(date that is lesser than the previous row) in rows ID 6 and 7 with the StartDate value of row ID 5.

    I have been trying to come up with a logic for quite some time but unable. Any help is much appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  ptp  20081231  See http://www.dbforums.com/showthread.php?t=1636750
    
    CREATE TABLE #patp (
       ID		INT		NOT NULL
    ,  StartDate	DATETIME	NOT NULL
       )
    
    INSERT INTO #patp (ID, StartDate)
       SELECT 1, 'Dec 30 2008 2:47PM' UNION
       SELECT 2, 'Dec 30 2008 2:49PM' UNION
       SELECT 3, 'Dec 30 2008 2:49PM' UNION
       SELECT 4, 'Dec 30 2008 2:53PM' UNION
       SELECT 5, 'Dec 30 2008 2:58PM' UNION
       SELECT 6, 'Dec 30 2008 2:49PM' UNION
       SELECT 7, 'Dec 30 2008 2:49PM' UNION
       SELECT 8, 'Dec 31 2008 3:55PM'
    
    UPDATE #patp
       SET StartDate = (SELECT Max(b.StartDate)
          FROM #patp AS b
          WHERE  b.ID <= #patp.ID)
    
    SELECT *
       FROM #patp
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •