Sagar Suri Sagar Suri - 1 year ago 35
MySQL Question

SQL Employee attendance record based on swipe-in and swipe-out time on particular date

I have a SQL table with the following employee's swipe-in and swipe-out data on a particular date.[Image Link below]

https://i.stack.imgur.com/j5wZv.png

I need to write an SQL query which produces the following results.[Image Link below]

https://i.stack.imgur.com/LK59I.png

Here in the result table working hours means the total time spent by the employee on that particular date.
This is the query what I have tried. I am getting the working_hours but not for a particular date.

SELECT emp_card_id
, CONCAT(CAST(
CASE WHEN SUM(UNIX_TIMESTAMP(out_time)
-UNIX_TIMESTAMP(in_time)) > 86400
THEN FLOOR(
SUM(UNIX_TIMESTAMP(out_time)
-UNIX_TIMESTAMP(in_time)) / 86400 )
ELSE 0 END AS CHAR)
, ' days' ) AS working_days
, SEC_TO_TIME(MOD(
SUM(UNIX_TIMESTAMP(out_time)
-UNIX_TIMESTAMP(in_time)) , 86400 )
) AS working_hours
FROM EmpAttendance.emp_attendance
GROUP
BY emp_card_id;


Please help. New to SQL queries.

Answer Source

I think you are on the right track. Just add your date into the SELECT portion of the query as well as the GROUP BY so that you group by both employee AND date:

SELECT emp_card_id, `date`
     , CONCAT(CAST(
       CASE WHEN SUM(UNIX_TIMESTAMP(out_time)
                    -UNIX_TIMESTAMP(in_time)) > 86400
            THEN FLOOR(
                 SUM(UNIX_TIMESTAMP(out_time)
                    -UNIX_TIMESTAMP(in_time)) / 86400 )
            ELSE 0 END AS CHAR)
         , ' days' ) AS working_days
        , SEC_TO_TIME(MOD(
                 SUM(UNIX_TIMESTAMP(out_time)
                    -UNIX_TIMESTAMP(in_time)) , 86400 )
             )  AS working_hours
  FROM EmpAttendance.emp_attendance
GROUP
    BY emp_card_id, `date`;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download