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

```
With `CROSS APPLY`:

```
DECLARE @d DATE = '20150320'

DECLARE @v TABLE
    (
      visitor_id INT ,
      visitor_name NVARCHAR(MAX)
    )
DECLARE @a TABLE
    (
      visitor_id INT ,
      type CHAR(3) ,
      time DATETIME
    )

INSERT  INTO @v
VALUES  ( 1, 'A' ),
        ( 2, 'B' ),
        ( 3, 'C' )

INSERT  INTO @a
VALUES  ( 1, 'in', '2015-03-20 19:32:27.513' ),
        ( 1, 'out', '2015-03-20 19:32:27.514' ),
        ( 1, 'in', '2015-03-20 19:32:27.515' ),
        ( 2, 'in', '2015-03-20 19:32:27.516' ),
        ( 2, 'out', '2015-03-20 19:32:27.517' ),
        ( 3, 'in', '2015-03-20 19:32:27.518' ),
        ( 3, 'out', '2015-03-20 19:32:27.519' ),
        ( 3, 'in', '2015-03-20 19:32:27.523' )

SELECT  *
FROM    @v v
        CROSS APPLY ( SELECT    *
                      FROM      ( SELECT TOP 1
                                            type
                                  FROM      @a a
                                  WHERE     a.visitor_id = v.visitor_id
                                            AND a.time >= @d
                                            AND a.time < DATEADD(dd, 1, @d)
                                  ORDER BY  time DESC
                                ) i
                      WHERE     type = 'in'
                    ) c

```

Output:

```
visitor_id  visitor_name    type
1           A               in 
3           C               in 

```

The principle:

1. First you are selecting all visitors.
2. Then you are applying to visitor last activity

> 
> 
> ```
> SELECT TOP 1
>         type
> FROM    @a a
> WHERE   a.visitor_id = v.visitor_id
>         AND a.time >= @d
>         AND a.time < DATEADD(dd, 1, @d)
> ORDER BY time DESC
> 
> ```
> 
> 

3. Then you are selecting from previous step in order to get empty set which will filter out visitors whose last activity was not 'in'. If last activity was 'in' you get one row in result and thus applying works. If last activity is 'out' then outer query will result in empty set, and by design `CROSS APPLY` will eliminate such visitor.