sql server - get the employee 'in/out' status based on 'enter/exit' column -
in sql server, how can employee 'in/out' status based on 'enter/exit' column? example, if last record employee 'enter', 'in'. if last record 'exit', 'out'. id=111, in_out should 'in', , id=222 should '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 should use case statement, following code won't work
select id, case when status = 'enter' 'in' when status = 'exit' 'out' else 'n/a' end in_out table1
if understand correctly can query below:
select id, case when [status] = 'enter' 'in' else 'out' end in_out ( select *, rown = row_number() over(partition id order [timestamp] desc) #timedata ) a.rown = 1
output below:
+-----+--------+ | id | in_out | +-----+--------+ | 111 | in | | 222 | out | | 333 | in | +-----+--------+
Comments
Post a Comment