I'm a newbie to SQL Server and am trying to develop a query which provides the 2nd session record for each participant in the data set.

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;

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · May 22, 2019 at 03:26 PM

@knovak If you need the second from the end session then your expected results are hard to interpret. For example, you have participant 19-BB-01 who attended sessions 1, 2, and 3 and so the second record from these 3 is picked, namely session 2 for this specific participant. However, the participant 19-AA-02 attended 2 sessions,, namely 2.1 and 3, and yet you would like the very last session to be included in the results, that is the session 3. Why is there a difference? For one participant you pick the last record while for other participant - one before the last. Please clarify. Thank you.

0 ·