Computing attendance hours

Good day!

I have 2 columns one for timein and one for timeout…and the data is from uploaded .xml file, my problem now is the saving of timeout in database…

in my .xml file I have this data:

Emp no Time In Time Out
100603 10/1/11 7:30 AM 10/1/11 6:00 PM
100603 10/2/11 8:00 AM 10/2/11 6:30 PM

and it saves in db
Emp no Time In Time Out
100603 2011-10-01 07:30:00 2011-10-01 18:00:00
100603 2011-10-02 08:00:00 2011-10-01 18:30:00

I want the timein and time out is formatted in 12 hours. As you can see the time out is formatted in 24 hours.

Thank you…

Actually, both time in (07:30:00) and time out (18:00:00) are formatted in 24 hour format, and that is the way the database stores the information. Maybe if you explain what you are trying to accomplish, one of the members here can point you in the correct direction.

I upload .xml using php and it saves to database.

I upload .xml using php and it saves to database.

I tried this data to upload:
100603 10/1/11 5:35 AM 10/1/11 1:35 PM // this is the exact schedule of employee and its 8 hours per day he time in exactly and timeout exactly also, so no problem in computing because it is 8 hours.
100603 10/2/11 5:25 AM 10/2/11 1:55 PM //in this example data the employee time in early in his schedule and time out late.
100603 10/3/11 5:40 AM 10/3/11 1:40 PM // in this example data the employee time in is late, so even he also late to timeout theres no exemption because he late on his work.

and it saves it database:
100603 2011-10-01 05:35:00 2011-10-01 13:35:00
100603 2011-10-02 05:25:00 2011-10-01 13:55:00
100603 2011-10-02 05:40:00 2011-10-01 13:40:00

Now, I know that the data stored time in database formatted in 24 hours. Honestly, I want to accomplish is to get the total hours of the employee based on the employee no.

and I tried this code:


select  sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein)) AS totalhours from employee;

and the result of this code is:

totalhours:
08:00:00
08:30:00
08:00:00

and the result is

the first is correct because the real schedule is 5:35 AM - 1:35 PM
the second is wrong it should be 8 hours only even he timein early and timeout late.
the third is also wrong because the employee is late to timein, even he also timeout late., it should be deduct or subtract in hours the late of employee.

I upload attendance .xml using php and it saves to database.

I tried this data to upload:
100603 10/1/11 5:35 AM 10/1/11 1:35 PM // this is the exact schedule of employee and its 8 hours per day he time in exactly and timeout exactly also, so no problem in computing because it is 8 hours.
100603 10/2/11 5:25 AM 10/2/11 1:55 PM //in this example data the employee time in early in his schedule and time out late. It should be only 8 hours.
100603 10/3/11 5:40 AM 10/3/11 1:40 PM // in this example data the employee time in is late, so even he also late to timeout there’s no exemption because he late on his work. So it should has deduction or minus in his total hours.

and it saves it database:
100603 2011-10-01 05:35:00 2011-10-01 13:35:00
100603 2011-10-02 05:25:00 2011-10-01 13:55:00
100603 2011-10-02 05:40:00 2011-10-01 13:40:00

I want to accomplish is to get the total hours of the employee based on the employee no. And even the employee get timein early before his time or late to timeout the hours computed only 8 hours. Honestly, I don’t have idea how can be possible it is.

and I tried this code for computing the hours per day:


select  sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein)) AS totalhours from employee;

and the result of this code is:

totalhours:
08:00:00
08:30:00
08:00:00

and the result is

the first is correct because the real schedule is 5:35 AM - 1:35 PM
the second is wrong it should be 8 hours only even he timein early and timeout late.
the third is also wrong because the employee is late to timein, even he also timeout late., it should be deduct or subtract in hours the late of employee.

I hope someone can help me. Thank you

What you need to do is confront the registered timein time with the regular timein time, and the same with the timeout time.

If the employee times in before the regular timein time, the difference is zero.
If the employee times in too late, you calculate the difference (in seconds).

If the employee times out after the regular timeout time, the difference is zero.
If the employee times out too early, you calculate the difference (in seconds).

Then you subtract the differences from the 8 hours.

Maybe it would be easier to do it in PHP, but in MySQL you could try something like this (I didn’t test it, I have no idea if you can use CASE’s like that in a time function):


sec_to_time(time_to_sec('08:00:00') + 
            case 
              when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0
              else time_to_sec('05:35:00') - time_to_sec(time(timein))
            end +
            case 
              when time_to_sec(time(timeout)) > time_to_sec('13:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('13:35:00')
            end
           )

Don’t let the ‘+’ signs confuse you, I wrote the calculations in a way that the result will be 0 or negative, so adding the negative difference will make sure it will be subtracted from the 8 hours.

I think your question is about who does what between PHP and mysql … this is my take:

Clearly for each employee you need to extract things which permit you to work out:

a) id
b) total hours worked
c) whether they were late or not

Get mysql to collect up the numbers, get PHP to work out the details.

So your result set wants to be something like:


$row[0] = array(
'EmpID' => '10023',
'total_hrs' => '08:00:00',
'start_time' => '05:40:00',
);

in pseudocode that would result in being processed with something like:


if( $late === false && $total_hrs > 8 ) 
   // pay the man

if($late === true )
   // work out penalty of lateness
   // see if they went home early - find that penalty 
   // add them together, and apply

Does that give you a leg up?

You could get mysql to return you a flag for “late” with some effort, but that would entail putting that logic into your database queries, when I suspect that start_time is could be quite a variable thing, as could the length of a working day, the insertion of lunch breaks etc.

These things are best left to PHP to perform, at least when starting out.

EDIT

Cripes, took me that long to create a reply! … well, at least you have 2 different views now …

The results are correct as required by your code. If you want to deduct time due to non-allowed clock-in and clock-out times, you need to have code that makes that correction.

I don’t know where you are located, and I am not a lawyer, but I would suggest you check with your legal adviser to be sure you are allowed to make those changes to the employees times.

Good day!

I have new sample for further understanding of my problem.

The real schedule of employee DS-1001 is from 9:35 PM to 5:35 AM he is night shift and the employee DS-1002 is from 5:35 AM to 1:35 PM he is morning shift.

here is the sample .xml file
EMP_NO Time In Time Out
DS-1001 10/1/11 9:35 PM 10/2/11 5:35 AM // this is the exact timein and timeout so theres no problem
DS-1001 10/2/11 9:00 PM 10/3/11 6:00 AM // in this sample the employee timein early and also timeout late.
DS-1001 10/3/11 10:00 PM 10/4/11 5:00 AM // in this sample the employee late to timein and he timeout early, so it should be subtract in his attendance total hours,
DS-1002 10/1/11 5:35 AM 10/1/11 1:35 PM// this is the exact timein and timeout so theres no problem
DS-1002 10/2/11 5:00 AM 10/2/11 2:00 PM// in this sample the employee timein early and also timeout late.
DS-1002 10/3/11 6:00 AM 10/3/11 1:00 PM// in this sample the employee late to timein and he timeout early, so it should be subtract in his attendance total hours,

and the data save in database is:
EMP_NO timein timeout total rendered
DS-1001 2011-10-01 21:35:00 2011-10-02 05:35:00 00:00:00 00:00:00
DS-1001 2011-10-02 21:00:00 2011-10-03 06:00:00 00:00:00 00:00:00
DS-1001 2011-10-03 22:00:00 2011-10-04 05:00:00 00:00:00 00:00:00
DS-1002 2011-10-01 05:35:00 2011-10-01 13:35:00 00:00:00 00:00:00
DS-1002 2011-10-02 05:00:00 2011-10-02 14:00:00 00:00:00 00:00:00
DS-1002 2011-10-03 06:00:00 2011-10-03 13:00:00 00:00:00 00:00:00

OT
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00

EMP_NO datatype is varchar
timein datatype is datetime
timeout datatype is datetime
total datatype is time
rendered datatype is time
OT datatype is time

the total field is the sum of the total hours of the employee
the rendered field is the exact 8 hours of employee or if the employee late like for example he is late or timeout early it should be subtracted and be output in rendered but normally it is 8 hours if his not late. In this field I don’t have idea how can i do that.
the OT field is the total - rendered field.

I dont know how can I insert that in my database.

I used this code to get the total hours but it did not work, and no error displayed.


INSERT INTO employee (total) SELECT sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));

I hope somebody can help me…

Thank you so much…

Good day!

I have new sample for further understanding of my problem.

The real schedule of employee DS-1001 is from 9:35 PM to 5:35 AM he is night shift and the employee DS-1002 is from 5:35 AM to 1:35 PM he is morning shift.

here is the sample .xml file
EMP_NO Time In Time Out
DS-1001 10/1/11 9:35 PM 10/2/11 5:35 AM // this is the exact timein and timeout so theres no problem
DS-1001 10/2/11 9:00 PM 10/3/11 6:00 AM // in this sample the employee timein early and also timeout late.
DS-1001 10/3/11 10:00 PM 10/4/11 5:00 AM // in this sample the employee late to timein and he timeout early, so it should be subtract in his attendance total hours,
DS-1002 10/1/11 5:35 AM 10/1/11 1:35 PM// this is the exact timein and timeout so theres no problem
DS-1002 10/2/11 5:00 AM 10/2/11 2:00 PM// in this sample the employee timein early and also timeout late.
DS-1002 10/3/11 6:00 AM 10/3/11 1:00 PM// in this sample the employee late to timein and he timeout early, so it should be subtract in his attendance total hours,

and the data save in database is:
EMP_NO timein timeout total rendered
DS-1001 2011-10-01 21:35:00 2011-10-02 05:35:00 00:00:00 00:00:00
DS-1001 2011-10-02 21:00:00 2011-10-03 06:00:00 00:00:00 00:00:00
DS-1001 2011-10-03 22:00:00 2011-10-04 05:00:00 00:00:00 00:00:00
DS-1002 2011-10-01 05:35:00 2011-10-01 13:35:00 00:00:00 00:00:00
DS-1002 2011-10-02 05:00:00 2011-10-02 14:00:00 00:00:00 00:00:00
DS-1002 2011-10-03 06:00:00 2011-10-03 13:00:00 00:00:00 00:00:00

OT
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00

EMP_NO datatype is varchar
timein datatype is datetime
timeout datatype is datetime
total datatype is time
rendered datatype is time
OT datatype is time

the total field is the sum of the total hours of the employee
the rendered field is the exact 8 hours of employee or if the employee late like for example he is late or timeout early it should be subtracted and be output in rendered but normally it is 8 hours if his not late. In this field I don’t have idea how can i do that.
the OT field is the total - rendered field.

I dont know how can I insert that in my database.

I used this code to get the total hours but it did not work, and no error displayed.


INSERT INTO employee (total) SELECT sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));

I hope somebody can help me…

Thank you so much…

tis is the vardump of my database:


-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.1.41


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema db_upload
--

CREATE DATABASE IF NOT EXISTS db_upload;
USE db_upload;

--
-- Definition of table `db_upload`.`employee`
--

DROP TABLE IF EXISTS `db_upload`.`employee`;
CREATE TABLE  `db_upload`.`employee` (
  `EMP_NO` varchar(50) NOT NULL,
  `timein` datetime NOT NULL,
  `timeout` datetime NOT NULL,
  `total` time NOT NULL,
  `rendered` time NOT NULL,
  `OT` time NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `db_upload`.`employee`
--

/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
INSERT INTO `db_upload`.`employee` (`EMP_NO`,`timein`,`timeout`,`total`,`rendered`,`OT`) VALUES 
 ('DS-1001','2011-10-01 21:35:00','2011-10-02 05:35:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1001','2011-10-02 21:00:00','2011-10-03 06:00:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1001','2011-10-03 22:00:00','2011-10-04 05:00:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1002','2011-10-01 05:35:00','2011-10-01 13:35:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1002','2011-10-02 05:00:00','2011-10-02 14:00:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1002','2011-10-03 06:00:00','2011-10-03 13:00:00','00:00:00','00:00:00','00:00:00');
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;




/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

tis is the vardump of my database:


-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.1.41


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema db_upload
--

CREATE DATABASE IF NOT EXISTS db_upload;
USE db_upload;

--
-- Definition of table `db_upload`.`employee`
--

DROP TABLE IF EXISTS `db_upload`.`employee`;
CREATE TABLE  `db_upload`.`employee` (
  `EMP_NO` varchar(50) NOT NULL,
  `timein` datetime NOT NULL,
  `timeout` datetime NOT NULL,
  `total` time NOT NULL,
  `rendered` time NOT NULL,
  `OT` time NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `db_upload`.`employee`
--

/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
INSERT INTO `db_upload`.`employee` (`EMP_NO`,`timein`,`timeout`,`total`,`rendered`,`OT`) VALUES 
 ('DS-1001','2011-10-01 21:35:00','2011-10-02 05:35:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1001','2011-10-02 21:00:00','2011-10-03 06:00:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1001','2011-10-03 22:00:00','2011-10-04 05:00:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1002','2011-10-01 05:35:00','2011-10-01 13:35:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1002','2011-10-02 05:00:00','2011-10-02 14:00:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1002','2011-10-03 06:00:00','2011-10-03 13:00:00','00:00:00','00:00:00','00:00:00');
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;




/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

tis is the vardump of my database:


-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.1.41


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema db_upload
--

CREATE DATABASE IF NOT EXISTS db_upload;
USE db_upload;

--
-- Definition of table `db_upload`.`employee`
--

DROP TABLE IF EXISTS `db_upload`.`employee`;
CREATE TABLE  `db_upload`.`employee` (
  `EMP_NO` varchar(50) NOT NULL,
  `timein` datetime NOT NULL,
  `timeout` datetime NOT NULL,
  `total` time NOT NULL,
  `rendered` time NOT NULL,
  `OT` time NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `db_upload`.`employee`
--

/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
INSERT INTO `db_upload`.`employee` (`EMP_NO`,`timein`,`timeout`,`total`,`rendered`,`OT`) VALUES 
 ('DS-1001','2011-10-01 21:35:00','2011-10-02 05:35:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1001','2011-10-02 21:00:00','2011-10-03 06:00:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1001','2011-10-03 22:00:00','2011-10-04 05:00:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1002','2011-10-01 05:35:00','2011-10-01 13:35:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1002','2011-10-02 05:00:00','2011-10-02 14:00:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1002','2011-10-03 06:00:00','2011-10-03 13:00:00','00:00:00','00:00:00','00:00:00');
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;




/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Two threads merged. Please don’t create a second thread for a problem when you already have one open (thread moved to MySQL forum)

I think your problem should be solvable with MySQL, I’ll give it go when I get home

I’mSorry…Thank you…I will test the query and i will tell you if my query run…I think my problem is on the rendered fields. Thank you so much

I change my syntax from insert to update because the total field will only update because they are in same table of timein and timeout, so I used this syntax and i test it in mysql:


UPDATE employee
SET total= sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));

When I used this syntax, the output was add in total field and it is correct…Now my problem is in the rendered, i dont know how can I display the rendered time the 8 hours or below 8 hours if he is late or early to timeout.Because now in total i only used update query and i run it in mysql.

In rendered it only gets the 8 hours from like for example 9:35 PM to 5:35 AM if he timein 9:00 PM he only get from 9:35PM - 5:35 AM or if he timeout 5:40 AM only the 9:35 PM - 5:35 AM he sum and insert in rendered field.

Thank you so much…

Thank you so much…

I have 3 shifts 21:35:00 to 05:35:00 , 05:35:00 to 13:35:00 , and 13:35:00 to 21:35:00

I have this data in my database:
EMP_NO| timein| timeout |total| rendered
DS-1001 2011-10-01 21:35:00 2011-10-02 05:35:00 08:00:00 00:00:00
DS-1001 2011-10-02 21:00:00 2011-10-03 06:00:00 09:00:00 00:00:00
DS-1001 2011-10-03 22:00:00 2011-10-04 05:00:00 07:00:00 00:00:00
DS-1002 2011-10-01 05:35:00 2011-10-01 13:35:00 08:00:00 00:00:00
DS-1002 2011-10-02 05:00:00 2011-10-02 14:00:00 09:00:00 00:00:00
DS-1002 2011-10-03 06:00:00 2011-10-03 13:00:00 07:00:00 00:00:00

OT
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00

I compute my total buy this code:


UPDATE employee
SET total= sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));

I test the code you suggesed and I add syntax for 21:35:00 - 05:35:00 shift:


select sec_to_time(time_to_sec('08:00:00') + 
            case 
              when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0
              else time_to_sec('05:35:00') - time_to_sec(time(timein))
            end +
            case 
              when time_to_sec(time(timeout)) > time_to_sec('13:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('13:35:00')
            end+
	    case 
              when time_to_sec(time(timein)) < time_to_sec('21:35:00') then 0
              else time_to_sec('21:35:00') - time_to_sec(time(timein))
            end +
            case 
              when time_to_sec(time(timeout)) > time_to_sec('05:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('05:35:00')
            end
           ) FROM employee;

and the output is
-16:00:00
-15:00:00
-18:00:00
08:00:00
08:00:00
07:00:00

I’m hoping that this output will insert in rendered field, I see only a problem in night shift 21:35:00 - 05:35:00
Thank you so much

Does this query give you the correct time worked by the employees’?

SELECT
      employee.EMP_NO
    , TIMESTAMPDIFF(HOUR,employee.timein,employee.timeout) AS worked_hours
    , ( minutes_worked.time_worked_minutes-(time_worked_hours*60)) AS worked_minutes
    , employee.timein
    , employee.timeout
    , total
    , rendered
    , OT
FROM
    (
        SELECT
              EMP_NO
            , TIMESTAMPDIFF(MINUTE,employee.timein,employee.timeout) AS time_worked_minutes
        FROM
            employee
    ) AS minutes_worked
INNER JOIN
    employee
        ON employee.EMP_NO=minutes_worked.EMP_NO
INNER JOIN        
    (
        SELECT
              EMP_NO
            , TIMESTAMPDIFF(HOUR,employee.timein,employee.timeout) AS time_worked_hours
        FROM
            employee
    ) AS hours_worked
        ON minutes_worked.EMP_NO=hours_worked.EMP_NO
GROUP BY
    employee.timein

Note that the TIMESTAMPDIFF() function may not be available on all servers (ie MSSQL, Oracle, etc) but there may an equivilant function available.

When I tried this code the data in my database goes like this:

vardump of my db;


-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.1.41


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema db_upload
--

CREATE DATABASE IF NOT EXISTS db_upload;
USE db_upload;

--
-- Definition of table `db_upload`.`employee`
--

DROP TABLE IF EXISTS `db_upload`.`employee`;
CREATE TABLE  `db_upload`.`employee` (
  `EMP_NO` varchar(50) NOT NULL,
  `timein` datetime NOT NULL,
  `timeout` datetime NOT NULL,
  `total` time NOT NULL,
  `rendered` time NOT NULL,
  `OT` time NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `db_upload`.`employee`
--

/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
INSERT INTO `db_upload`.`employee` (`EMP_NO`,`timein`,`timeout`,`total`,`rendered`,`OT`) VALUES 
 ('DS-1001','2011-10-01 21:35:00','2011-10-02 05:35:00','08:00:00','08:00:00','00:00:00'),
 ('DS-1001','2011-10-02 21:00:00','2011-10-03 06:00:00','09:00:00','08:00:00','01:00:00'),
 ('DS-1001','2011-10-03 22:00:00','2011-10-04 05:00:00','07:00:00','07:00:00','00:00:00'),
 ('DS-1002','2011-10-01 05:35:00','2011-10-01 13:35:00','08:00:00','08:00:00','00:00:00'),
 ('DS-1002','2011-10-02 05:00:00','2011-10-02 14:00:00','09:00:00','08:00:00','01:00:00'),
 ('DS-1002','2011-10-03 06:00:00','2011-10-03 13:00:00','07:00:00','08:00:00','-01:00:00'),
 ('DS-1003','2011-10-01 13:35:00','2011-10-01 21:35:00','08:00:00','08:00:00','00:00:00'),
 ('DS-1003','2011-10-02 13:00:00','2011-10-02 22:00:00','09:00:00','08:00:00','01:00:00'),
 ('DS-1003','2011-10-03 14:00:00','2011-10-03 21:00:00','07:00:00','08:00:00','-01:00:00');
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;




/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;


I have this query to compute the total, rendered, OT
I used this query to insert the total, rendered and OT


---Getting total----
UPDATE employee SET total = sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));

---GEtting Rendered----
UPDATE employee SET rendered = sec_to_time(time_to_sec('08:00:00') + 
            case 
              when time_to_sec(time(timein)) < time_to_sec('21:35:00') then 0
              else time_to_sec('21:35:00') - time_to_sec(time(timein))
            end +
            case 
              when time_to_sec(time(timeout)) > time_to_sec('05:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('05:35:00')
            end);

----GEtting OT-----
UPDATE employee SET total = sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));

Is there a way to correct my syntax in rendered so that I could compute my OT correct…Thank you