Earliest Open Date Data

[Schaeffrcc]

I have a table that is used to keep history of client movement and most clients have 3 or 4 records in the table.

I.E:

Table 1
Seq 1 2 3 4
Name Tom Bill Tom Jan
Open Date 1/2/2000 1/5/2002 2/5/2002 5/2/2003
Close date 2/1/2001 Null Null 6/2/2003
Case Type 2 5 5 3

I am looking to get data that is opened on or after 1/1/2000(>=)and have a case type of 2 or 5.

The Query Returns Seq 1,2,3 and I want to get only records 1 and 2.

I only want to get each Name once but get Tom Twice because he has two recods that meet the criteria. Is there a way to get distinct name records with the earliest Open Date or Seq. number.


I hope this makes Sense.

Thanks,

Thomas.

 

[ClaireHCM]

select min(seq) as seq from table1 where name in (select name from table1 where opendate >='1/1/2002' and type in (2,5)) group by name order by seq