selecting point in time when change was made

Question

Say i have the following table:

id Name Status Date
1 John Working 11/11/2003
2 John Working 03/03/2004
3 John Quit 04/04/2004
4 John Quit 04/05/2004
5 John Quit 04/06/2004
6 Joey Working 03/05/2009
7 Joey Working 02/06/2009
8 Joey Quit 02/07/2009
9 Joey Quit 02/08/2009
10 Joey Quit 02/09/2009

I want to get the date when the change between working and quit occured, so that i get:

3 John Quit 04/04/2004
8 Joey Quit 02/07/2009

How would i do that?

Answer 1

select ID, Name, Status, Date
from tableName
where ID in (
    select min(ID)
    from tableName
    where Status = 'Quit'
)

This will get the first record that has the status of 'Quit' for each person. It is not necessarily the chronologically first record though. If that is what you're are looking for let me know. Also, if so, can you be sure there won't be any duplicated names; this could be problematic if there are two different John's or Joey's.

Answer 2

select id, name, date 
from tableName WHERE date = 
(select MIN(date) 
             FROM tableName 
             WHERE NAME='<name>' AND date >= (select MAX(date) 
             FROM tableName WHERE NAME='<name>' AND status = 'Working')
)

Hopefully I didn't screw things up badly in the nested query there. This would only give you 1 user, you could wrap it in a function and call it passing the username or do something like make a temp table with distinct name inserted into it and loop through those names.

A lot of options exist for bringing it form just one user to all users.