On 6/22/2012 12:18 AM, Anupam Karmarkar wrote:
Thanks Rick for your reply,
Here i am asking about logic to perpare query or whole query itself.
A set-based approach to doing the basic task is to convert your set of
start/stop times into duration values. The timediff() function mentioned
already is a good way to do this.
CREATE TEMPORARY TABLE tmpHours SELECT EmployeeID, timediff(logouttime,
logintime) as duration FROM sourcetable;
At this point, you have a temporary table of (EmployeeID, duration). It
becomes very simple to write a summary query:
SELECT employeeid, sum(duration) as totalhours from tmpHours group by
employeeid;
If you want to breakdown your final report by other values (by date, by
week, by shift, etc) then you need to compute those and add them to the
tmpHours table when you create it.
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
