merge rows based on column I have attendance_log table :

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| a_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| a_emp_id | int(11)     | YES  | MUL | NULL    |                |
| a_date   | date        | YES  |     | NULL    |                |
| a_time   | time        | YES  |     | NULL    |                |
| a_status | varchar(45) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+


the date represented as below:

| 3654 |        2 | 2014-04-01 | 09:02:00 | IN       |
| 3658 |        2 | 2014-04-01 | 13:00:00 | OUT      |
| 3664 |        2 | 2014-04-01 | 16:30:00 | IN       |
| 3670 |        2 | 2014-04-01 | 21:30:00 | OUT      |


I need to view the data per date per employees as below:

a_emp_id, a_date, a_time as IN, a_status, a_time as OUT.


for example:

2    | 2014-04-01  | 09:02:00 |IN   | 13:00:00 |OUT  | 16:30:00  | IN  | 21:30:00 | OUT 


Or:

How can i calculate the time for each day based on IN,OUT status?

Thanks
PROPOSED QUERY

select SEC_TO_TIME(IF(WorkSec&lt;0,UNIX_TIMESTAMP(),0)+WorkSec) WorkTime
from (select SUM(UNIX_TIMESTAMP(concat(a_date,' ',a_time))*
IF(a_status='IN',-1,1)) WorkSec
from worktable where emp_id = 2) A;


SAMPLE DATA

mysql&gt; use test
Database changed
mysql&gt; drop table if exists worktable;
Query OK, 0 rows affected (0.13 sec)

mysql&gt; create table worktable
    -&gt; (a_id int not null auto_increment,
    -&gt; emp_id int not null,a_date date,
    -&gt; a_time time,a_status varchar(45),
    -&gt; primary key (a_id),key (emp_id));
Query OK, 0 rows affected (0.52 sec)

mysql&gt; insert into worktable
    -&gt; (emp_id,a_date,a_time,a_status) values
    -&gt; (2,'2014-04-01','09:02:00','IN'),
    -&gt; (2,'2014-04-01','13:00:00','OUT'),
    -&gt; (2,'2014-04-01','16:30:00','IN'),
    -&gt; (2,'2014-04-01','21:30:00','OUT');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql&gt; select * from worktable;
+------+--------+------------+----------+----------+
| a_id | emp_id | a_date     | a_time   | a_status |
+------+--------+------------+----------+----------+
|    1 |      2 | 2014-04-01 | 09:02:00 | IN       |
|    2 |      2 | 2014-04-01 | 13:00:00 | OUT      |
|    3 |      2 | 2014-04-01 | 16:30:00 | IN       |
|    4 |      2 | 2014-04-01 | 21:30:00 | OUT      |
+------+--------+------------+----------+----------+
4 rows in set (0.00 sec)

mysql&gt;


RUN PROPOSED QUERY FOR EMPLOYEE 2 (WHO IS OUT)

mysql&gt; select SEC_TO_TIME(IF(WorkSec&lt;0,UNIX_TIMESTAMP(),0)+WorkSec) WorkTime
    -&gt; from (select SUM(UNIX_TIMESTAMP(concat(a_date,' ',a_time))*
    -&gt; IF(a_status='IN',-1,1)) WorkSec
    -&gt; from worktable where emp_id = 2) A;
+-----------------+
| WorkTime        |
+-----------------+
| 08:58:00.000000 |
+-----------------+
1 row in set (0.00 sec)

mysql&gt;


BRING EMPLOYEE 2 IN TO WORK TODAY

mysql&gt; insert into worktable
    -&gt; (emp_id,a_date,a_time,a_status) values
    -&gt; (2,'2014-04-02','09:02:00','IN');
Query OK, 1 row affected (0.06 sec)

mysql&gt; select * from worktable;
+------+--------+------------+----------+----------+
| a_id | emp_id | a_date     | a_time   | a_status |
+------+--------+------------+----------+----------+
|    1 |      2 | 2014-04-01 | 09:02:00 | IN       |
|    2 |      2 | 2014-04-01 | 13:00:00 | OUT      |
|    3 |      2 | 2014-04-01 | 16:30:00 | IN       |
|    4 |      2 | 2014-04-01 | 21:30:00 | OUT      |
|    5 |      2 | 2014-04-02 | 09:02:00 | IN       |
+------+--------+------------+----------+----------+
5 rows in set (0.00 sec)

mysql&gt;


RUN PROPOSED QUERY FOR EMPLOYEE 2 (WHO IS IN TODAY)

mysql&gt; select now();
+---------------------+
| now()               |
+---------------------+
| 2014-04-02 10:20:49 |
+---------------------+
1 row in set (0.00 sec)

mysql&gt; select SEC_TO_TIME(IF(WorkSec&lt;0,UNIX_TIMESTAMP(),0)+WorkSec) WorkTime
    -&gt; from (select SUM(UNIX_TIMESTAMP(concat(a_date,' ',a_time))*
    -&gt; IF(a_status='IN',-1,1)) WorkSec
    -&gt; from worktable where emp_id = 2) A;
+-----------------+
| WorkTime        |
+-----------------+
| 10:16:51.000000 |
+-----------------+
1 row in set (0.00 sec)

mysql&gt;


EPILOGUE

When an employee is out, the WorkTime is evidently a static datetime value.

When an employee is in, the WorkTime is evidently a moving datetime value.

I will leave it to you to tweek the query to set it for a day, a week, for a group of employees, or whatever you boundaries you need.

GIVE IT A TRY !!!