MySQL Attendance Calculation

Question

Here i have table attendances

I need result as shown below

How can i achieve this in mysql without using any programming language

Sql File is Attendances.sql

Answer 1

We can try a pivot query approach, aggregating by user and date:

SELECT
    user_id,
    DATE(date_time) AS date,
    TIMESTAMPDIFF(MINUTE,
                  MAX(CASE WHEN status = 'IN' THEN date_time END),
                  MAX(CASE WHEN status = 'OUT' THEN date_time END)) / 60.0 AS hours
FROM yourTable
GROUP BY
    user_id,
    DATE(date_time);

The caveats of this answer are many. It assumes that there would be only one IN and OUT entry, per user, per day. If a period could cross over dates, then my answer might not generate correct results. Also, if an IN or OUT value be missing, then NULL would be reported for the hours value.