consolidate tables

**homer.favenir** · Apr 15, 2008 04:28

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

**longneck** · Apr 15, 2008 06:32

please post the CREATE TABLE statements for all 4 tables and the UNION query that takes so long.

**homer.favenir** · Apr 16, 2008 02:25

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

**homer.favenir** · Apr 16, 2008 03:18

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

**longneck** · Apr 16, 2008 07:09

can you explain what all that calculation mess in the middle of your query is doing?

**homer.favenir** · Apr 16, 2008 19:15

Originally Posted by longneck

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

**longneck** · Apr 17, 2008 06:39

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.

**homer.favenir** · Apr 17, 2008 19:42

Originally Posted by longneck

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.

User Tag List

Thread: consolidate tables

Thread Tools

Display

consolidate tables

Bookmarks

Bookmarks

Posting Permissions