CODEWITHSUNDEEP
sql-server
Eric Wu
Eric Wu

Reputation: 5

get the employee 'in/out' status based on 'enter/exit' column

In SQL Server, how can I get the employee 'in/out' status based on the 'enter/exit' column? For example, if the last record for an employee is 'enter', then he is 'in'. If the last record is 'exit', then he is 'out'. For id=111, the in_out should be 'in', and id=222 should be 'out'

+-----+---------------------+------------+
| id  |      timestamp      |   status   |
+-----+---------------------+------------+
| 111 | 01/01/2017 07:00:10 | enter      |
| 222 | 01/01/2017 01:10:29 | enter      |
| 111 | 01/01/2017 18:20:17 | exit       |
| 111 | 01/02/2017 08:20:34 | enter      |
| 333 | 01/02/2017 06:20:11 | enter      |
| 222 | 01/02/2017 10:10:47 | exit       |
+-----+---------------------+------------+

I understand I should use case statement, but the following code won't work 

select id, case   
       when status = 'enter' then 'in'  
       when status = 'exit' then 'out'  
       else 'n/a'  
       end as in_out  
from table1

Upvotes: 0

Views: 366

Answers (2)

James K. Lowden
James K. Lowden

Reputation: 7837

The trick is to find the first exit following each enter. 

select in.id,
       case coalesce(min(out.status), in.status)
       when 'enter' then 'in'
       when 'exit'  then 'out'
       else 'n/a' -- I suggest: coalesce(out.status, in.status) + '??'
       end as 'status'
from table1 as in
left
 join table1 as out
on in.id = out.id
and in.timestamp <= out.timestamp
and in.status = 'enter'
and out.status = 'exit'
group by in.id, in.status

This query finds the minimum exit for each {id, timestamp} enter pair. It assumes there's no exit before enter (no one in the building). That requires separate validation.

Whenever I munge the data for a report like this, I try not to squelch unexpected input, but rather let it flow through. If you turn everything that's not enter or exit into n/a, at some point you're going to have to find out what's producing those n/a's. Might as well print it out in the report; it will make your job easier.

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

If I understand correctly you can query as below:

Select Id, case when [status] = 'enter' then 'in' else 'out' end as In_out 
   from (
    Select *, RowN = row_number() over(partition by id order by [timestamp] desc) from #timedata
) a Where a.RowN = 1

Output as below:

+-----+--------+
| Id  | In_out |
+-----+--------+
| 111 | in     |
| 222 | out    |
| 333 | in     |
+-----+--------+

Upvotes: 1

Related Questions