Skip to content
Advertisement

Get users attendance entry and exit in one row SQL Server

I have a table with all entries for employees. I need to get all the working hours and the entry and exit time of the user in one record. The table is like this:

enter image description here

How can I do that and also in case there is some missing entries or exit. Like one employee will have entry with no exit in some odd cases.

Advertisement

Answer

This adds LEAD entrytype to make sure there is a corresponding OUT row. Also, it divides the date difference in minutes by 60.0 (added decimal)

select t.empId EmpID, cast(datetime as date) [Day], datetime [Timein],  next_datetime [Timeout],
       datediff(mi, datetime, next_datetime)/60.0 TotalHours
from (select t.*,
             lead(datetime) over (partition by empid order by datetime) as next_datetime,
             lead(entrytype) over (partition by empid order by datetime) as next_entrytype
      from t
     ) t
where entrytype = 'IN'
      and next_entrytype='Out';
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement