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:
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';