I have a table with employees attendance logs from sensor machines, employees can have multiple IN & OUT entries in a single day, I want to calculate the number of hours an employess remained IN (total worked hours) by ignoring the Time OUT I've been looking for this solution for two days, but can't get the required result.
╔════════╦═══════╦═════════════════════════╦════════════╗
║ emp_id ║ shift ║ check_time ║ check_type ║
╠════════╬═══════╬═════════════════════════╬════════════╣
║ 2 ║ 1 ║ 2018-02-22 07:00:44.000 ║ C-IN ║
║ 2 ║ 1 ║ 2018-02-22 13:00:35.000 ║ B-OUT ║
║ 2 ║ 1 ║ 2018-02-22 13:30:46.000 ║ B-IN ║
║ 2 ║ 5 ║ 2018-02-22 16:00:55.000 ║ C-OUT ║
╚════════╩═══════╩═════════════════════════╩════════════╝
╔════════╦══════════╦═════════════════════════╦════════════╗
║ emp_id ║ IN Hrs ║ Date ║ OUT HRS ║
╠════════╬══════════╬═════════════════════════╬════════════╣
║ 2 ║ 08:30 ║ 2018-01-22 ║ 00:30 ║
╚════════╩══════════╩═════════════════════════╩════════════╝
Assuming the IN and OUT are always in pair.
You can make use of LEAD() window function to get next check_time. And use CASE WHEN condition to determine it is IN or OUT time
select emp_id,
in_hrs = sum(in_mins) / 60.0,
check_date = convert(date, check_time),
out_hrs = sum(out_mins) / 60.0
from
(
select *,
in_mins = CASE WHEN check_type in ('C-IN', 'B-IN')
AND LEAD(check_type) OVER (PARTITION BY emp_id ORDER BY check_time) in ('C-OUT', 'B-OUT')
THEN DATEDIFF(MINUTE,
check_time,
LEAD(check_time) OVER (PARTITION BY emp_id ORDER BY check_time))
ELSE 0
END,
out_mins= CASE WHEN check_type in ('C-OUT', 'B-OUT')
AND LEAD(check_type) OVER (PARTITION BY emp_id ORDER BY check_time) in ('B-IN')
THEN DATEDIFF(MINUTE,
check_time,
LEAD(check_time) OVER (PARTITION BY emp_id ORDER BY check_time))
ELSE 0
END
from checkin_out_log
) d
group by emp_id, convert(date, check_time)
Edit : added condition to validate cases where IN without OUT or vice-versa. The in/out will be ignore and no calculation.
The added condition are
LEAD(check_type) OVER (PARTITION BY emp_id ORDER BY check_time) in ('C-OUT', 'B-OUT')