FROMDATE TODATE CATEGORY LEVEL 01/01/1998 01/01/1999 O 1 01/02/1999 05/23/1999 O 2 05/24/1999 08/14/2001 A 1 08/15/2001 08/01/2002 R 1 08/02/2002 10/10/2003 R 3 10/11/2003 10/31/2003 R 4 11/01/2003 12/01/2003 O 2 12/02/2003 02/15/2004 X 1 02/16/2004 R 3I need to find what level this person was the first time they became a category R. The categories and levels have no predefined sequence (i.e. R doesn't always follow O or A and 2 does not always follow 1, etc).
select personno, category, level from misshist; where personno + dtoc(fromdate,1) in; (select personno + dtoc(min(fromdate),1) from misshist where category = 'R' group by personno)It seems like I can't rely on getting the correct level if I do:
select personno, category, level, min(fromdate); from misshist; where category = 'R'; group by personno(I left personno out of the above table. It would be a unique identifier by individual. The records I showed above would be for 1 individual (i.e. they would all have the same personno)).