AlexT (11/13/2008)
yes whatever,select distinct a.PersonID,a.Version, a.DEDate , case when b.PersonID is null then 0 else 1 end AS [Is ImProper]
from PersonRecord a
left join (
select a.PersonID
from PersonRecord a
inner join PersonRecord b on a.PersonID = b.PersonID and a.Version>b.Version and a.DEDate<b.DEDate
) b on a.PersonID = b.PersonID
but initial tast was just get bad data entry dates
Thanks, this achieves the desired results, and it's a straight-forward, easy-to-read solution.
When I tested it on my 5 million row table, total execution time was 164580 Milliseconds duration
Edited: Although I think I would put the "select distinct" in the subselect, for cases where one PersonId has multiple rows that are out of order.