Good article!

This type of query can be handled much easier in SQL 2005, but I would still try to avoid a correlated subquery in SQL 2000. You can move the query into a derived table and make it inline. Obviously, this is over simplifying the solution but you get the idea.

SELECT

PersonRecord.*,

Improper.[IsImProper]

FROM PersonRecord

INNER JOIN(

SELECT

a.PersonID,

MAX(CASE

WHEN a.Version < b.version and A.DEDate > B.DEDate

THEN 1

ELSE 0

END) as IsImProper

FROM PersonRecord A

INNER JOIN PersonRecord B

ON A.PersonID = B.PersonID AND

A.Version = B.Version -1

GROUP BY A.PersonId

) AS Improper

ON Improper.[PersonID] = PersonRecord.PersonID