I need to create a select query that will look at a list of employees and determine if they are in or out. the table will have the following data:
I need to find any employee that has more then 1 record and then toggle a new column to identify if the employee is checked in or out based on the date/time.
999, 2016-01-01: 9:00 -- This would have a column with 1 added
999, 2016-01-01: 15:00 -- This would have a column with 2 added
999, 2016-01-01: 18:00 -- This would go back to 1
I hope it should work
SELECT employeeid,Cast (date/time as date), (RANK() OVER (PARTITION BY employeeid ORDER BY date/time)) as 'in/out' FROM table GROUP BY employeeid,Cast (date/time as date)