dfarran (11/13/2008)

---

It is also possible to do the query with just left joins,

SELECT a.PersonID, a.Version , a.dedate,

[is improper] = CASE WHEN MAX(c.version) IS NOT NULL THEN 1

ELSE 0 END

FROM personrecord a

LEFT JOIN personrecord b ON b.personid = a.personid

LEFT JOIN personrecord c ON c.personid = a.personid

AND c.version >= b.version

AND c.dedate < b.dedate

GROUP BY a.PersonID, a.Version, a.dedate

with the primary key on the table (personid, version) this performs slightly better than the cross join with both the small(8) and medium(circa .75m) rowcount.

I would avoid using the version = version -1 because this does not cater for missing versions (try doubling the version numbers in test data)