If a participant has attended a split session (2.1 and 2.2), then I would like the session date of the most recent session. Because participants do not always attend all the available sessions, we need to look at their actual second session rather than the fact they attended session 2. Here is a sample of the data along with the SQL code that I have tried. (I get results using this query, but the query is not grouping the split sessions such that it ignores one of the split sessions if a participant has both a 2.1 and a 2.2 session. Thanks for any help you can provide!
SELECT Particip,Max(SessionDate) as MaxSessionDate, Convert(Int,SessionNo) as MaxSessionNo FROM (SELECT tblSessions.*, ROW_NUMBER() OVER (PARTITION BY Particip ORDER BY SessionDate ASC) AS RANK FROM tblSessions) T WHERE T.RANK=2 GROUP BY Particip, Convert(Int,SessionNo) ORDER BY Particip, MaxSessionDate ASC;