Calculate Working hours
Hello,
I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. Please help to reduce the execution time of this query or if there is any other way to calculate working hours
The following query take 63.499 sec
SELECT sql_calc_found_rows gstime,
MAX(stoptime) AS mx,
MIN(starttime) AS mn,
Sec_to_time(SUM(Time_to_sec(Timediff(stoptime, starttime)))) AS totalworktime
FROM (SELECT gstime,
gstime AS stoptime,
Coalesce((SELECT MAX(b.gstime)
FROM xydata b
WHERE objectid = '17'
AND clientid = '1'
AND gstime > '2010-04-20 08:22:27'
AND gstime < '2010-04-26 10:22:27'
AND b.objectid = a.objectid
AND b.gstime < a.gstime), gstime) AS starttime
FROM xydata a
INNER JOIN fm4features f
ON f.id = a.id
WHERE objectid = '17'
AND clientid = '1'
AND gstime > '2010-04-20 08:22:27'
AND gstime < '2010-04-26 10:22:27'
AND f.dataid = '1'
AND f.VALUE = '1') derived
GROUP BY Date_format(gstime, '%Y-%m-%d')
ORDER BY gstime ASC
please help me how do i change the query to reduce the sec.
Bakthavachalam E
Subject
Written By
Posted
Calculate Working hours
April 29, 2010 03:44AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.