Problem in Case Statement

[rhoda rose barrera]

Good day!

I am new in case statement,I have this query statement to compute and update the total. rendered and OT fields. I encountered problem in my case statement to compute/get the rendered. I have 3 shifts 21:35:00 - 05:35:00 , 05:.35:00-13:35:00 , and 13:35:00 - 21:35:00

---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));

As you can see only one shift I have in rendered query, I want to put also the 2 shifts, but when I tried only one shift has correct data.

this is my 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','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 */;

As you can see only the empno DS-1001 has the correct computation of rendered.

Thank you