Basically the data is access data to a building. I want to establish if the person is in or out of the building. There are two access points one for IN and one for OUT. the SQL I have so far is as follows which is the select from a view called AD_in_OUT LOG
SELECT Username, thumbnailPhoto, [Display Name], Department, USERID, email, BADGENUMBER, NAME, CHECKTYPE, CHECKTIME
FROM dbo.[AD_in_Out LOG]
So I need to group by USERID and get the latest time for each USERID from CHECKTIME. I then need to establish if the latest time was an OUT or IN from the CHECKTYPE column. It seems simple, but I am not getting the required results. What is the best way to do this as the view looks up from 3 different tables as well so server overhead needs to be minimal.