SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Apr 2008
    Location
    Manila, Philippines
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    consolidate tables

    hi,
    how can i consolidate my 4 tables?
    i already used union but its a long code to make and it takes 85 seconds to execute.


    thanks

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    please post the CREATE TABLE statements for all 4 tables and the UNION query that takes so long.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Member
    Join Date
    Apr 2008
    Location
    Manila, Philippines
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    hi,
    thanks for the reply

    this is my long code, is there any other way to simplify it?

    Code:
    SELECT
    coding_log.`date`,
    coding_log.last_name,
    `user`.tl,
    batch_log.`client`,
    batch_log.service,
    
    sum(coding_log.break_hours) as 'Total Break',
    
    Hour(if (min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00',
      subtime(max(coding_log.time_end),'16:00'), if(min(coding_log.time_start)
      and max(coding_log.time_end) <= '16:00', '0', subtime(max(coding_log.time_end),
      min(coding_log.time_start))))) + minute(if (min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00',
      subtime(max(coding_log.time_end),'16:00'), if(min(coding_log.time_start)
      and max(coding_log.time_end) <= '16:00', '0', subtime(max(coding_log.time_end),
      min(coding_log.time_start)))))/60 as 'Over Time',
    
    HOUR(subtime(SEC_TO_TIME(SUM(TIME_TO_SEC(SUBTIME(coding_log.time_end,coding_log.time_start)))),
      if(min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00',
      subtime(max(coding_log.time_end),'16:00'), if(min(coding_log.time_start)
      and max(coding_log.time_end) <= '16:00', '0', subtime(max(coding_log.time_end),
      min(coding_log.time_start)))))) +
    MINUTE(subtime(SEC_TO_TIME(SUM(TIME_TO_SEC(SUBTIME(coding_log.time_end,coding_log.time_start)))),
      if(min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00',
      subtime(max(coding_log.time_end),'16:00'), if(min(coding_log.time_start)
      and max(coding_log.time_end) <= '16:00', '0', subtime(max(coding_log.time_end),
      min(coding_log.time_start))))))/60 as 'Regular Hours',
    
    sum(Hour(IF(coding_log.`Time_End`<coding_log.`Time_Start`,
        addtime('24:00:00', timediff(coding_log.`Time_End`, coding_log.`Time_Start`)),
        timediff(coding_log.`Time_End`, coding_log.`Time_Start`)))) +
       (sum(minute(IF(coding_log.`Time_End`<coding_log.`Time_Start`,
        addtime('24:00:00', timediff(coding_log.`Time_End`, coding_log.`Time_Start`)),
         timediff(coding_log.`Time_End`, coding_log.`Time_Start`))))/60) as 'Total Man Hours',
    "coding_log" as 'Data Process'
    
    FROM
    coding_log left join `user` on (`user`.last_name = coding_log.last_name)
    inner join batch_log on (batch_log.job_number = coding_log.job_no)
    where batch_log.total_batches is not null
    
    group by
    coding_log.`date`,
    coding_log.last_name,
    batch_log.`client`
    
    union
    
    SELECT
    key_entry_log.`date`,
    key_entry_log.last_name,
    `user`.tl,
    batch_log.`client`,
    batch_log.`service`,
    sum(key_entry_log.break_hours) as 'Total Break',
    Hour(if (min(key_entry_log.time_start) <= '16:00' and max(key_entry_log.time_end) >= '16:00',
      subtime(max(key_entry_log.time_end),'16:00'), if(min(key_entry_log.time_start)
      and max(key_entry_log.time_end) <= '16:00', '0', subtime(max(key_entry_log.time_end),
      min(key_entry_log.time_start))))) + minute(if (min(key_entry_log.time_start) <= '16:00' and max(key_entry_log.time_end) >= '16:00',
      subtime(max(key_entry_log.time_end),'16:00'), if(min(key_entry_log.time_start)
      and max(key_entry_log.time_end) <= '16:00', '0', subtime(max(key_entry_log.time_end),
      min(key_entry_log.time_start)))))/60 as 'Over Time',
    
    HOUR(subtime(SEC_TO_TIME(SUM(TIME_TO_SEC(SUBTIME(key_entry_log.time_end,key_entry_log.time_start)))),
      if(min(key_entry_log.time_start) <= '16:00' and max(key_entry_log.time_end) >= '16:00',
      subtime(max(key_entry_log.time_end),'16:00'), if(min(key_entry_log.time_start)
      and max(key_entry_log.time_end) <= '16:00', '0', subtime(max(key_entry_log.time_end),
      min(key_entry_log.time_start)))))) +
    MINUTE(subtime(SEC_TO_TIME(SUM(TIME_TO_SEC(SUBTIME(key_entry_log.time_end,key_entry_log.time_start)))),
      if(min(key_entry_log.time_start) <= '16:00' and max(key_entry_log.time_end) >= '16:00',
      subtime(max(key_entry_log.time_end),'16:00'), if(min(key_entry_log.time_start)
      and max(key_entry_log.time_end) <= '16:00', '0', subtime(max(key_entry_log.time_end),
      min(key_entry_log.time_start))))))/60 as 'Regular Hours',
    
    sum(Hour(IF(key_entry_log.`Time_End`<key_entry_log.`Time_Start`,
        addtime('24:00:00', timediff(key_entry_log.`Time_End`, key_entry_log.`Time_Start`)),
        timediff(key_entry_log.`Time_End`, key_entry_log.`Time_Start`)))) +
       (sum(minute(IF(key_entry_log.`Time_End`<key_entry_log.`Time_Start`,
        addtime('24:00:00', timediff(key_entry_log.`Time_End`, key_entry_log.`Time_Start`)),
         timediff(key_entry_log.`Time_End`, key_entry_log.`Time_Start`))))/60) as 'Total Man Hours',
    "key_entry_log" as 'Data Process'
    
    FROM
    key_entry_log left join `user` on (`user`.last_name = key_entry_log.last_name)
    inner join batch_log on (batch_log.job_number = key_entry_log.job_no)
    where batch_log.total_batches is not null
    
    group by
    key_entry_log.`date`,
    key_entry_log.last_name,
    batch_log.`client`
    
    union
    
    SELECT
    scan_log.`date`,
    scan_log.last_name,
    `user`.tl,
    batch_log.`client`,
    batch_log.`service`,
    sum(scan_log.break_hours) as 'Total Break',
    Hour(if (min(scan_log.time_start) <= '16:00' and max(scan_log.time_end) >= '16:00',
      subtime(max(scan_log.time_end),'16:00'), if(min(scan_log.time_start)
      and max(scan_log.time_end) <= '16:00', '0', subtime(max(scan_log.time_end),
      min(scan_log.time_start))))) + minute(if (min(scan_log.time_start) <= '16:00' and max(scan_log.time_end) >= '16:00',
      subtime(max(scan_log.time_end),'16:00'), if(min(scan_log.time_start)
      and max(scan_log.time_end) <= '16:00', '0', subtime(max(scan_log.time_end),
      min(scan_log.time_start)))))/60 as 'Over Time',
    
    HOUR(subtime(SEC_TO_TIME(SUM(TIME_TO_SEC(SUBTIME(scan_log.time_end,scan_log.time_start)))),
      if(min(scan_log.time_start) <= '16:00' and max(scan_log.time_end) >= '16:00',
      subtime(max(scan_log.time_end),'16:00'), if(min(scan_log.time_start)
      and max(scan_log.time_end) <= '16:00', '0', subtime(max(scan_log.time_end),
      min(scan_log.time_start)))))) +
    MINUTE(subtime(SEC_TO_TIME(SUM(TIME_TO_SEC(SUBTIME(scan_log.time_end,scan_log.time_start)))),
      if(min(scan_log.time_start) <= '16:00' and max(scan_log.time_end) >= '16:00',
      subtime(max(scan_log.time_end),'16:00'), if(min(scan_log.time_start)
      and max(scan_log.time_end) <= '16:00', '0', subtime(max(scan_log.time_end),
      min(scan_log.time_start))))))/60 as 'Regular Hours',
    
    sum(Hour(IF(scan_log.`Time_End`<scan_log.`Time_Start`,
        addtime('24:00:00', timediff(scan_log.`Time_End`, scan_log.`Time_Start`)),
        timediff(scan_log.`Time_End`, scan_log.`Time_Start`)))) +
       (sum(minute(IF(scan_log.`Time_End`<scan_log.`Time_Start`,
        addtime('24:00:00', timediff(scan_log.`Time_End`, scan_log.`Time_Start`)),
         timediff(scan_log.`Time_End`, scan_log.`Time_Start`))))/60) as 'Total Man Hours',
    "scan_log" as 'Data Process'
    
    FROM
    scan_log left join `user` on (`user`.last_name = scan_log.last_name)
    inner join batch_log on (batch_log.job_number = scan_log.job_no)
    
    group by
    scan_log.`date`,
    scan_log.last_name,
    batch_log.`client`
    
    union
    
    SELECT
    qc_log.`date`,
    qc_log.last_name,
    `user`.tl,
    batch_log.`client`,
    batch_log.`service`,
    sum(qc_log.break_hours) as 'Total Break',
    Hour(if (min(qc_log.time_start) <= '16:00' and max(qc_log.time_end) >= '16:00',
      subtime(max(qc_log.time_end),'16:00'), if(min(qc_log.time_start)
      and max(qc_log.time_end) <= '16:00', '0', subtime(max(qc_log.time_end),
      min(qc_log.time_start))))) + minute(if (min(qc_log.time_start) <= '16:00' and max(qc_log.time_end) >= '16:00',
      subtime(max(qc_log.time_end),'16:00'), if(min(qc_log.time_start)
      and max(qc_log.time_end) <= '16:00', '0', subtime(max(qc_log.time_end),
      min(qc_log.time_start)))))/60 as 'Over Time',
    
    HOUR(subtime(SEC_TO_TIME(SUM(TIME_TO_SEC(SUBTIME(qc_log.time_end,qc_log.time_start)))),
      if(min(qc_log.time_start) <= '16:00' and max(qc_log.time_end) >= '16:00',
      subtime(max(qc_log.time_end),'16:00'), if(min(qc_log.time_start)
      and max(qc_log.time_end) <= '16:00', '0', subtime(max(qc_log.time_end),
      min(qc_log.time_start)))))) +
    MINUTE(subtime(SEC_TO_TIME(SUM(TIME_TO_SEC(SUBTIME(qc_log.time_end,qc_log.time_start)))),
      if(min(qc_log.time_start) <= '16:00' and max(qc_log.time_end) >= '16:00',
      subtime(max(qc_log.time_end),'16:00'), if(min(qc_log.time_start)
      and max(qc_log.time_end) <= '16:00', '0', subtime(max(qc_log.time_end),
      min(qc_log.time_start))))))/60 as 'Regular Hours',
    
    sum(Hour(IF(qc_log.`Time_End`<qc_log.`Time_Start`,
        addtime('24:00:00', timediff(qc_log.`Time_End`, qc_log.`Time_Start`)),
        timediff(qc_log.`Time_End`, qc_log.`Time_Start`)))) +
       (sum(minute(IF(qc_log.`Time_End`<qc_log.`Time_Start`,
        addtime('24:00:00', timediff(qc_log.`Time_End`, qc_log.`Time_Start`)),
         timediff(qc_log.`Time_End`, qc_log.`Time_Start`))))/60) as 'Total Man Hours',
    "qc_log" as 'Data Process'
    
    FROM
      qc_log
    left join `user` on (`user`.last_name = qc_log.last_name)
    inner join batch_log on (batch_log.job_number = qc_log.job_no)
    where batch_log.total_batches is not null and qc_log.last_name = 'chua'
    
    group by
    qc_log.`date`,
    qc_log.last_name,
    batch_log.`client`
    
    
    order by
    `date` desc

  4. #4
    SitePoint Member
    Join Date
    Apr 2008
    Location
    Manila, Philippines
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    these are my tables:

    Code:
    DROP TABLE IF EXISTS `des`.`batch_log`;
    CREATE TABLE `des`.`batch_log` (
    `batchID` int(11) NOT NULL auto_increment,
    `Job_Number` varchar(20) NOT NULL,
    `Job_Name` varchar(100) NOT NULL,
    `Publisher` varchar(100) NOT NULL,
    `No_Of_Columns` int(11) NOT NULL,
    `No_Of_Pages` int(11) NOT NULL,
    `Shipment_Date` date NOT NULL,
    `Estimated_Records` int(11) NOT NULL,
    `Date` date NOT NULL,
    `Time_Start` time NOT NULL,
    `Time_End` time default NULL,
    `Batch_Prefix` varchar(10) default NULL,
    `Total_Batches` int(5) default NULL,
    `Archive_Clerk` varchar(20) NOT NULL default '',
    `Status` varchar(10) NOT NULL,
    `No_of_Persons` int(10) unsigned NOT NULL default '0',
    `Client` varchar(45) NOT NULL default '',
    `Service` varchar(10) NOT NULL,
    PRIMARY KEY USING BTREE (`batchID`,`Client`,`Service`),
    KEY `Index_2` (`Job_Number`,`Job_Name`,`Publisher`,`No_Of_Columns`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    CREATE TABLE `des`.`coding_log` (
    `coID` int(11) NOT NULL auto_increment,
    `Date` date NOT NULL,
    `Job_No` varchar(20) NOT NULL,
    `Job_Name` varchar(100) NOT NULL,
    `Total_Batches` int(11) NOT NULL,
    `Batch` varchar(10) NOT NULL,
    `Page_Range` varchar(10) NOT NULL,
    `Time_Start` time default NULL,
    `Time_End` time default NULL,
    `Total_Batches_Coded` int(11) NOT NULL,
    `Status` varchar(15) NOT NULL,
    `Last_Name` varchar(45) NOT NULL default '',
    `coding_process` varchar(30) NOT NULL,
    `break_hours` float default '0',
    PRIMARY KEY (`coID`),
    KEY `Index_2` USING BTREE (`Date`,`Job_No`,`Time_Start`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    CREATE TABLE `des`.`key_entry_log` (
    `keID` int(11) NOT NULL auto_increment,
    `Date` date default NULL,
    `Job_No` varchar(20) NOT NULL,
    `Job_Name` varchar(100) NOT NULL,
    `Total_Batches` int(5) default NULL,
    `Batch` varchar(11) NOT NULL default '0',
    `Page` char(10) default NULL,
    `Time_Start` time default NULL,
    `Time_End` time default NULL,
    `Records` int(11) default NULL,
    `Status` varchar(10) default NULL,
    `Last_Name` varchar(45) NOT NULL default '',
    `break_hours` float default '0',
    PRIMARY KEY (`keID`),
    KEY `Index_2` (`Date`,`Job_No`,`Last_Name`,`Time_Start`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    CREATE TABLE `des`.`scan_log` (
    `idscan_log` int(10) unsigned NOT NULL auto_increment,
    `Job_No` varchar(45) NOT NULL default '',
    `Job_Name` varchar(100) NOT NULL default '',
    `Total_Batches` int(10) unsigned NOT NULL default '0',
    `Batch` varchar(45) NOT NULL default '',
    `Page` varchar(45) NOT NULL default '',
    `Last_Name` varchar(45) NOT NULL default '0',
    `Time_Start` time default NULL,
    `Time_End` time default NULL,
    `Records` int(10) unsigned default '0',
    `Status` varchar(45) NOT NULL default '',
    `Date` date NOT NULL default '0000-00-00',
    `break_hours` float default '0',
    `bna_process` varchar(30) default NULL,
    PRIMARY KEY (`idscan_log`),
    KEY `Index_2` (`Date`,`Job_No`,`Last_Name`,`Time_Start`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    CREATE TABLE `des`.`qc_log` (
    `idQC` int(10) unsigned NOT NULL auto_increment,
    `Date` date NOT NULL default '0000-00-00',
    `Last_Name` varchar(45) NOT NULL default '',
    `Job_No` varchar(45) NOT NULL default '',
    `Job_Name` varchar(100) NOT NULL default '',
    `Part` int(10) unsigned NOT NULL default '0',
    `Batch` varchar(45) NOT NULL default '',
    `Records` int(10) unsigned NOT NULL default '0',
    `Status` varchar(45) NOT NULL default '',
    `QC Process` varchar(45) NOT NULL default '',
    `Time_Start` time NOT NULL default '00:00:00',
    `Time_End` time NOT NULL default '00:00:00',
    `Total_Batches` int(10) unsigned NOT NULL default '0',
    `Required_Fields` varchar(45) NOT NULL default '',
    `break_hours` float default '0',
    PRIMARY KEY (`idQC`),
    KEY `Index_2` (`Date`,`Last_Name`,`Job_No`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    CREATE TABLE `des`.`user` (
    `coderID` int(10) unsigned NOT NULL auto_increment,
    `OP_#` int(10) unsigned NOT NULL default '0',
    `Last_Name` varchar(45) NOT NULL default '',
    `First_Name` varchar(45) NOT NULL default '',
    `Group` varchar(45) NOT NULL default '',
    `TL` varchar(45) NOT NULL default '',
    `userlevel` int(10) unsigned NOT NULL default '0',
    `Reports_To` int(10) unsigned NOT NULL default '0',
    PRIMARY KEY (`coderID`,`TL`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    what i need is i have to consolidate 4 tables (coding_log, key_entry_log, scan_log and qc_log), and it should be join to 2 tables (batch_log and users).

    Last edited by homer.favenir; Apr 16, 2008 at 02:27. Reason: change "these is my table" to "these are my table"

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    can you explain what all that calculation mess in the middle of your query is doing?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SitePoint Member
    Join Date
    Apr 2008
    Location
    Manila, Philippines
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    can you explain what all that calculation mess in the middle of your query is doing?
    it computes the 'total man hours', 'over time', and 'regular hours'.

    its a long code and it works.
    and i need to make it simplify cause it executes too long.

    thanks

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i'll help you rewrite the query, but you need to do most of the heavy lifting. describe, in english, how to calculate man hours, overtime and regular hours and i'll see if we can simplify it.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  8. #8
    SitePoint Member
    Join Date
    Apr 2008
    Location
    Manila, Philippines
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post

    Quote Originally Posted by longneck View Post
    i'll help you rewrite the query, but you need to do most of the heavy lifting. describe, in english, how to calculate man hours, overtime and regular hours and i'll see if we can simplify it.
    ok, lets do it step by step:
    1. overtime:
    16:00 = actual end of shift (time out)
    for the hour
    Code:
    hour(if min(time_start) <= 16:00 and max(time_end) >= 16:00
             {
              subtime(max(time_end),'16:00')
             }
    else if
             {
              if(min(time_start) and max(time_end) <= '16:00'
                   {
                    '0'
                    }
                     else
                         {
                          subtime(max(time_end), min(time_start)
                          }
               )
    for the minutes:
    Code:
    minute(if the min(time_start) <= 16:00 and max(time_end) >= 16:00
             {
              subtime(max(time_end),'16:00')
             }
    else if
             {
              if(min(time_start) and max(time_end) <= '16:00'
                   {
                    '0'
                    }
                     else
                         {
                          subtime(max(time_end), min(time_start)
                          }
               )
    2. manhours
    for the hour:
    Code:
    sum(Hour(IF(`time_end`<`time_start`,
                 {
                  addtime('24:00:00', timediff(`time_end`, `time_start`)),
                  }
                  elseif
                        {
                        timediff(`time_end`, `time_start`)))) 
                         }
    +
    for the minute:
    Code:
    (sum(minute(IF(`time_end`<`time_start`,
                   {
                   addtime('24:00:00', timediff(`time_end`, `time_start`)),
                    }
                    elseif
                          {           
                          timediff(`time_end`, `time_start`))))/60)
                          }
    3. regular hour
    Code:
    manhour - overtime = regular hour
    hopefully i explained it very well, though im not very good in english so i just wrote it in codes.

    thanks so much.
    Last edited by homer.favenir; Apr 17, 2008 at 18:44. Reason: change code


Bookmarks

Posting Permissions

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