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

```
This uses a CTE to find the activity record with the greatest Activity\_Time where the Activity\_Type = 'IN' and assigns it RowNum 1. Then you can INNER JOIN the CTE to the Visitor table, filtering by the CTE results where RowNum = 1.

```
; WITH VisAct AS(
    SELECT act.Visitor_ID
            , ROW_NUMBER() OVER(PARTITION BY Visitor_ID ORDER BY Activity_Time DESC) AS RowNum
      FROM Visitor_Activity act
     WHERE act.Activity_Type = 'IN'
       AND act.Activity_Time >= CAST(GETDATE() AS DATE)
)
SELECT vis.Visitor_ID, vis.Visitor_name
  FROM Visitor vis
 INNER JOIN VisAct act
    ON act.Visitor_ID = vis.Visitor_ID
 WHERE act.Row_Num = 1       

```