views:

285

answers:

4

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

+1  A: 

I'm not sure if I get your right. You want the latest date and status for any ID?

That would be:

SELECT    a.ID, a.StatusDate, a.Status
FROM      Table AS a
INNER JOIN 
(
    SELECT   ID, Max(StatusDate) AS StatusDate
    FROM     Table
    GROUP BY ID
) AS b
ON        a.ID = b.ID
AND       a.StatusDate = b.StatusDate

Now, what table do you want to update with this? What do you mean by "run an insert into on all the records where [Status] = 'Start'"? Did you create a second table and insert this date into it?

EDIT:

Okay, so I guess you want to update the data in this newly created table then?

Try this (I hope I have no syntax error in this, I don't have an SQL server here right now to try it):

UPDATE    c
SET       c.StatusDate = a.StatusDate,
          c.Status = a.Status
FROM      NewTable AS c
INNER JOIN Table AS a
ON        c.ID = a.ID
INNER JOIN 
(
    SELECT   ID, Max(StatusDate) AS StatusDate
    FROM     Table
    GROUP BY ID
) AS b
ON        a.ID = b.ID
AND       a.StatusDate = b.StatusDate
Maximilian Mayerl
IMHO, if you need clarification you could ask in comments as is done before. Thanks!
Shankar Ramachandran
A: 

I presume you just want to UPDATE all results with Status="Start" to Status="Stop" AND [Status Date] = now() (now() or something similar depending on the database)?

Sorry if that sounds unclear, but I'm not really sure what you are asking.

Del
Not quite. What I want is to select (in this case) the status date, and status from the 4th record (which can be identified as the one with the latest date) and then update the unique record (see [Edit] above) with these values
Karl
A: 

Why do you need to UPDATE anything? If you can determine from the data which value has the highest date, there is no reason to store it. All that does is introduce the possibility for inconsistency ... now you need to update everything every time a single row is added, changed or deleted. Yuck.

Aaron Bertrand
I agree, but I'll only be doing this once. I am migrating this dataset (along with many others with different formats) into one standardised file. It isn't a dynamic process.
Karl
A: 

Does this do what you want? It creates a new record for every record that has a status of start and adds a status of stop but only if a stop record does not currently exist.

Insert into my table (ID  Start Date   Status Date      Status)
select ID  Start Date   Status Date      'Stop'
From mytable  mt 
left join mytable mt2 on mt.id = mt2.id and mt2.status = 'stop'
where mt.status = 'Start'
and mt2.id is null
HLGEM