Hi guys, the title doesn't really sum up what I have been trying to do but I'll try and explain it a little better.

We have a user audit table that contains userauditref (unique identifier for the table), userref, username, obsolete, editdate (and others of no interest to me :) ).

So, there are multiple entries per each user and for each entry the obsolete field is a boolean flag for what it was at the time of the user being updated.

I am trying to pull a report that will look at the records where the obsolete flag has gone from 1 to 0 (i.e. unobsoleted) however as this could have happened many times, I want the most recent.

I attempted to use derived tables but still seem to get duplicates. I also tried using nested selects in my joins but I'm not sure what the best way to do it.

My logic was to select all distinct users where the obsolete flag is 1 and the edit date is the most recent, do the same in another table for where the flag is 0 and then do a comparison on the two tables to see what date is greater to obtain my answer.

Am I missing a better way of doing this or can you help prompt me as to how to get this to work? TIA

Please could you offer a brief explanation of it? If I can make it out correctly, it seems like it only pulls back the ones where obsolete is is 1 but I'm not sure how it would distinguish which entries have been 1 and gone to 0?

;With CTE
AS
(
select
A.*,
ROW_NUMBER() over (partition by a.userref order by editdate) as Seq
from Audit A
)
SELECT *
FROM
(
SELECT c1.userref,c1.editdate AS MovedOutDate,c2.Obsolete AS MovedInDate,..any other columns,
ROW_NUMBER() OVER (PARTITION BY c1.userref ORDER BY c1.edidate DESC) AS Rn
FROM CTE c1
INNER JOIN CTE c2
ON c1.userref = c2.userref
AND c1.Seq = c2.Seq-1
AND c1.Obsolete = 1
AND c2.Obsolete = 0
)r
WHERE Rn = 1