Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Problem in Case Statement

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your CASE calculations have hardcoded the start/end times of only one of your three shifts

    besides, i think you should let guido on sitepoint forums continue to help you, rather than taking this same problem to other forums... guido is real good and you should follow his advice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by r937 View Post
    your CASE calculations have hardcoded the start/end times of only one of your three shifts

    besides, i think you should let guido on sitepoint forums continue to help you, rather than taking this same problem to other forums... guido is real good and you should follow his advice
    You mean the separate sql statement...?

    This case statement is for only one shift;
    Code:
    select 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) FROM employee;
    and now I want to add case for the shift 05:35:00 - 13:35:00 and 13:35:00 - 21:35:00. I really need this to solve, and I don't have idea what syntax should i need..

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •