How to get break time of the Employees using MySQL Query only

Databases
1

Hi Guys,

Above is my table tbl_staff in my database, is there a way that I can get the total break hours of each employees? using Mysql query only.

Here is my sample query that I am using right now.



SELECT id, staff_id, staffname, card_day, sum(t_diff)
FROM (
SELECT *
FROM (
  SELECT
    id,
    staff_ID,
    STAFFNAME,
    DATE(CARDTIME) AS card_day,
    unix_timestamp(next_CARDTIME)-unix_timestamp(CARDTIME) As t_diff,
    CASE WHEN
      CASE WHEN next_CARDTIME IS NULL THEN @n:=-1 ELSE @n:=@n+1 END MOD 2 = 0
      THEN 'Work' ELSE 'Break'
    END AS type
  FROM (
    SELECT
      t1.id,
      t1.staff_ID,
      t1.STAFFNAME,
      t1.CARDTIME,
      MIN(t2.CARDTIME) next_CARDTIME
    FROM
      tablename t1 LEFT JOIN tablename t2
      ON (t1.id, t1.staff_ID) = (t2.id, t2.staff_ID)
         AND DATE(t1.cardtime)=DATE(t2.cardtime)
         AND t1.cardtime<t2.cardtime
    GROUP BY
      t1.id, t1.staff_ID, t1.STAFFNAME, t1.CARDTIME
    ORDER BY
      t1.id, t1.staff_ID, t1.STAFFNAME, t1.CARDTIME
    ) s, (SELECT @n:=-1) r
  ) s
WHERE t_diff IS NOT NULL) s
WHERE Type='Break'
GROUP BY id, staff_id, staffname, card_day

The query returns break hours , but I want it to be more standard without using local variable @n, does anyone knows how to do it?
Thanks in advance guys