I have the following problem:
Suppose I have a table with the following fields: [ID] [Start Date] [Status Date] [Status Description]
[ID] is not unique, and so I may have:
ID Start Date Status Date Status
123 01/01/2009 01/01/2009 Start
123 01/01/2009 01/02/2009 Change
123 01/01/2009 01/03/2009 Change
123 01/01/2009 01/07/2009 Stop
What I want to do is the following: run an insert into on all the records where [Status] = 'Start'.
When that is done, the part that I don't know how to do is then the following: I want to update [Status Date] and [Status] to that of the final status date. I.e. what I want is:
ID Start Date Status Date Status
123 01/01/2009 01/07/2009 Stop
Any suggestions?
[EDIT]
I'm using SQL Server 2008
With insert into I mean create a new table and insert into it only those records that have status "Start", thereby achieving a table with unique IDs