Help with Script

• kalyur

  Newbie

  Points: 1

  More actions

  December 31, 2008 at 1:20 pm

  #128875

  I have a situation where the following data is in a table

  ID Start Date

  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

  What I am trying to do is write a clean up script that checks the last record(ID=6)Start Date and if it's lesser than the previous Start Date (ie ID=5) update record ID = 6 with the previous Start Date(ID=5).

  Any help on this is appreciated.

• null-512060

  SSChasing Mays

  Points: 613

  More actions

  December 31, 2008 at 2:14 pm

  #919829

  Please try this:

  CREATE TABLE dbo.TestDate (ID TINYINT IDENTITY

  ,StartDate DATETIME)

  INSERT INTO dbo.TestDate (StartDate)

  SELECT 'Dec 30 2008 2:47PM'

  UNION ALL

  SELECT 'Dec 30 2008 2:48PM'

  UNION ALL

  SELECT 'Dec 30 2008 2:59PM'

  UNION ALL

  SELECT 'Dec 30 2008 2:53PM'

  UNION ALL

  SELECT 'Dec 30 2008 2:58PM'

  UNION ALL

  SELECT 'Dec 30 2008 2:49PM'

  DECLARE @rowCount INT

  WHILE ISNULL(@rowCount,1) > 0

  BEGIN

  UPDATE a

  SET a.StartDate = b.StartDate

  FROM dbo.TestDate a

  INNER JOIN dbo.TestDate b

  ON b.ID = (SELECT MAX(ID)

  FROM dbo.TestDate

  WHERE ID < a.ID)

  AND a.StartDate < b.StartDate

  SELECT @rowCount = @@ROWCOUNT

  END

  SELECT ID

  ,StartDate

  FROM dbo.TestDate

  Hope this helps.