Question: So I have a Visitor table, and a Visitor\_activity table. Say: 

Visitor

```
Visitor_ID Int
Visitor_name varchar(20)

```

Visitor\_Activity

```
ID Int
Visitor_ID Int
Activity_Type char(3) -- values IN or OUT
Activity_Time datetime

```

Visitors might sign in and out multiple times in a day.

I'd like a nice query to tell me all visitors who are in: i.e. the last activity for today (on activity\_time) was an "IN" not an "OUT". Any advice much appreciated. 
It's T-SQL by the way, but I think it's more of an in-principle question.

Answer: 
```
with visitorsInOut as (
    select Visitor_id,
           max(case when Activity_Type = 'in' then Activity_Time else null end) inTime,
           max(case when Activity_Type = 'out' then Activity_Time else null end) outTime
    from   Visitor_Activity
    where  datediff(dd, Activity_Time, getdate()) = 0
    group by Visitor_id)
select Visitor_id
from   visitorsInOut
where  inTime > outTime or outTime is null

```
You can pull the most recent action for each visitor, and then only return those where the last action for today was to check in. 

```
SELECT v.Visitor_ID, v.Visitor_Name, va.Activity_Type, va.Activity_Time
    FROM Visitor AS v
    INNER JOIN (SELECT Visitor_ID, Activity_Type, Activity_Time, RANK() OVER (PARTITION BY Visitor_ID ORDER BY Activity_Time DESC) AS LastAction
        FROM Visitor_Activity
        -- checks for today, can be omitted if you still want 
        -- to see someone checked in from yesterday
        WHERE DATEDIFF(d, 0, Activity_Time) = DATEDIFF(d, 0, getdate())
        ) AS va ON va.Visitor_ID = v.Visitor_ID 
    WHERE LastAction = 1
    AND Activity_Type = 'IN'

```