AlexT (11/13/2008)
yes sorry, my mistake. if you don't mind you can try thisselect 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 distinct 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
This query does produce the desired effect, and yes, moving the distinct inside the subselect does result in a significant performance lift. Total execution time was 144470 Milliseconds! (vs. previous run time of was 164580 Milliseconds)