Get first datetime and last datetime on a single row

[davidmatthewjames]

Good morning all, I'm trying to create a report which shows me the time a user logs in and log out of our system. Maybe I'm approaching this the wrong way, but I thought I'd use ROW_NUM over Partition to get the first and the last entry, but by doing so I'm getting the right times but over 2 rows rather than one. Can someone help me figure this out?

I need to get the first entry in the table that has a status of Available, and the last entry in the table with a status of Gone Home. Ideally I would like to run this for the month and have an output of Date, UserID, Login, Logout.

Table

Code & Result

Kind regards

David

[RogierPronk]

Have you considered a MAX? Not sure if it's working:

SELECT UserID, MAX(CASE WHEN ... END) AS LogInTime, MAX(CASE WHEN ... END) AS LogInTime
FROM GetLogTimes
WHERE
GROUP BY UserID

[davidmatthewjames]

Perfect thank you.