Carla Wilson (11/12/2008)

---

Adam Haines (11/12/2008)

---

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.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

correction: the CASE clause should read:

WHEN a.DEDate > B.DEDate

but this does give the correct results.

The 2005 (partition) technique does not give the desired result of marking all rows for the personID.