luger luger - 1 month ago 6
SQL Question

Adding a Check in - Out indicator in sql select statement

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:

employeeid,date/time

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.

Ex.

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 need this to look at all the lines in the table. Is this possible. I do not have access to make any changes to the original table.

Answer

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)