Take the MAX and MIN status, grouped by ID.  You can then write your CASE expression similar to how you've done above. John