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