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
Code:
---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:
Code:
-- 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
dBforums
>
Problem in Case Statement
10-25-11, 21:50
Linear Mode
