sql-serverrelational-databasedynamic-queries

SQL Server calculating working hours with multiple IN OUT


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.

Logs Table is as under (checkin_out_log)

╔════════╦═══════╦═════════════════════════╦════════════╗
║ 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      ║
╚════════╩═══════╩═════════════════════════╩════════════╝

Desired OUTPUT is :

╔════════╦══════════╦═════════════════════════╦════════════╗
║ emp_id ║  IN Hrs  ║       Date              ║ OUT HRS    ║
╠════════╬══════════╬═════════════════════════╬════════════╣
║      2 ║ 08:30    ║ 2018-01-22              ║ 00:30      ║
╚════════╩══════════╩═════════════════════════╩════════════╝

Here is the data in query

Here is what I get in results with data shown in screenshot with your query, please guide whats wrong


Solution

  • 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')