| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
04-15-08, 03:38
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
simplified codes
|
|
hi,
how can i simplify these codes?
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',
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',
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',
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))))) as 'Reg Hour'
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`
order by
coding_log.`date` desc
and the output is this
HTML Code:
<table border=1 cellspacing=1 cellpadding=0><tr>
<th>date</th><th>last_name</th><th>tl</th><th>client</th><th>service</th><th>Total Break</th><th>Over Time</th><th>Regular Hours</th><th>Total Man Hours</th></tr>
<tr>
<td>2008-04-15</td><td>Ocampo</td><td>Mercy</td><td>IDEARC/VER</td><td>YELLOW</td><td>0</td><td>0.0000</td><td>0.2500</td><td>0.2500</td></tr>
<tr>
<td>2008-04-15</td><td>Saludes</td><td>Nila</td><td>IDEARC/VER</td><td>YELLOW</td><td>2</td><td>0.0000</td><td>5.0833</td><td>5.0833</td></tr>
<tr>
<td>2008-04-14</td><td>Agustin, J</td><td>Noel</td><td>IDEARC/VER</td><td>YELLOW</td><td>1.5</td><td>4.0000</td><td>8.6833</td><td>12.6833</td></tr>
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
Last edited by homer.favenir; 04-15-08 at 03:58.
|
04-15-08, 07:02
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
do us a favour and give us a hint
what are you trying to do?
|
|
04-15-08, 07:18
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
|
|
Quote:
|
Originally Posted by r937
do us a favour and give us a hint
what are you trying to do?
|
i have 4 tables (table1, table2, table3 and table4)
i need to know what are the employees doing for the whole day?
are they in table1, table2, table3 or in table4?
i need to consolidate all tables using union, to get 1 report for all tables.
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
04-15-08, 07:23
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Quote:
|
Originally Posted by homer.favenir
i have 4 tables (table1, table2, table3 and table4)
|
no, you do not, you have three tables: coding_log, `user`, batch_log
and you don't need to UNION them
|
|
04-15-08, 07:30
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
Quote:
|
Originally Posted by r937
no, you do not, you have three tables: coding_log, `user`, batch_log
and you don't need to UNION them
|
sorry the code above is not complete coz its a long code actually.
but i have 4 main tables
coding_log, key_entry_log, scan_log and qc_log.
and the other tables is JOIN to link them to the main tables.
what function rathern than union?
how can i consolidate the 4 main tables?
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
Last edited by homer.favenir; 04-15-08 at 07:35.
|
04-15-08, 07:42
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Quote:
|
Originally Posted by homer.favenir
how can i consolidate the 4 main tables?
|
Code:
SELECT x.source_table
, x.time_start
, x.time_end
, x.last_name
, u.t1
FROM users AS u
INNER
JOIN (
SELECT 'coding_log' AS source_table
, time_start
, time_end
, last_name
FROM coding_log
UNION ALL
SELECT 'key_entry_log'
, time_start
, time_end
, last_name
FROM key_entry_log
UNION ALL
SELECT 'scan_log'
, time_start
, time_end
, last_name
FROM scan_log
UNION ALL
SELECT 'qc_log'
, time_start
, time_end
, last_name
FROM qc_log
) AS x
ON x.last_name = u.last_name
|
|
04-15-08, 21:54
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
Quote:
|
Originally Posted by r937
Code:
SELECT x.source_table
, x.time_start
, x.time_end
, x.last_name
, u.t1
FROM users AS u
INNER
JOIN (
SELECT 'coding_log' AS source_table
, time_start
, time_end
, last_name
FROM coding_log
UNION ALL
SELECT 'key_entry_log'
, time_start
, time_end
, last_name
FROM key_entry_log
UNION ALL
SELECT 'scan_log'
, time_start
, time_end
, last_name
FROM scan_log
UNION ALL
SELECT 'qc_log'
, time_start
, time_end
, last_name
FROM qc_log
) AS x
ON x.last_name = u.last_name
|
many thanks!
but how about
Code:
batch_log.`client`,
batch_log.service,
above?
i cant join them to your code.
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
04-18-08, 00:37
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
hi,
the query above takes 23 minutes to execute.
its too slow.
it is simplified but it doesnt cut the time from executing.
please advice
thanks
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
04-18-08, 00:45
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
i am sorry, i cannot help you
i have no idea what you are doing
|
|
04-18-08, 02:51
|
|
Registered User
|
|
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
|
|
Quote:
|
Originally Posted by r937
i am sorry, i cannot help you
i have no idea what you are doing
|
i have figured out the solutions to the problem of joining the
Code:
batch_log.`client`,
batch_log.service,
but i have still a problem, could you help me with this?
i encountered "LOST CONNECTION TO MYSQL SERVER DURING QUERY".
thus the connection timed out during execution?
thanks
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
|
|
04-18-08, 03:48
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
simplified codes
