2

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

1 Answer 1

0

PROPOSED QUERY

select SEC_TO_TIME(IF(WorkSec<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> use test
Database changed
mysql> drop table if exists worktable;
Query OK, 0 rows affected (0.13 sec)

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

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

mysql> 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>

RUN PROPOSED QUERY FOR EMPLOYEE 2 (WHO IS OUT)

mysql> select SEC_TO_TIME(IF(WorkSec<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;
+-----------------+
| WorkTime        |
+-----------------+
| 08:58:00.000000 |
+-----------------+
1 row in set (0.00 sec)

mysql>

BRING EMPLOYEE 2 IN TO WORK TODAY

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

mysql> 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>

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

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

mysql> select SEC_TO_TIME(IF(WorkSec<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;
+-----------------+
| WorkTime        |
+-----------------+
| 10:16:51.000000 |
+-----------------+
1 row in set (0.00 sec)

mysql>

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 !!!

1
  • Working perfectly. that's what i'm looking for. Thanks a lot :)
    – kholioeg
    Apr 2, 2014 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.