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: 
One way to solve this is to use a correlated not exists predicate:

```
select Activity_Time, Visitor_ID 
from Visitor_Activity t1
where Activity_Type = 'IN'
and not exists (
    select 1 
    from Visitor_Activity
    where Activity_Type = 'OUT'
    and Visitor_ID = t1.Visitor_ID 
    and Activity_Time > t1.Activity_Time
    and cast(Activity_Time as date) = cast(t1.Activity_Time as date)
    )

```

This basically says *get all visitor\_id that have type = IN for which there doesn't exists any type = OUT record with a later time (on the same date)*.

[Sample SQL Fiddle](http://www.sqlfiddle.com/#!6/2cfd6/1)
```
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

```