I was curious how the different techniques performed, so I loaded up the PersonRecord table with more data using a Numbers table (please see article http://www.sqlservercentral.com/articles/TSQL/62867/ [/url]for Numbers table)

insert into PersonRecord

select N,0,'11/12/2008'

from Numbers

where N between 4 and 11000

insert into PersonRecord

select N,1,'11/13/2008'

from Numbers

where N between 4 and 11000

insert into PersonRecord

select N,2,'11/10/2008'

from Numbers

where (N between 4 and 11000) and N % 100 = 0

So total of 11000 PersonIds, and 22118 total rows in the PersonRecord table.

I then recorded the execution time for 4 of the techniques.

Original Query 610 Milliseconds duration

Adam Haines

- inner joins and CASE 576 Milliseconds duration

James Goodwin

- Left join on inner join 656 Milliseconds duration

Matt C - SQL2005

row_number() over (partition) 860 Milliseconds duration

Just thought you would find this interesting.

(edited to add link to Numbers table article)