From: Shawn Green
Date: June 22 2012 1:04pm
Subject: Re: Need Query Help
List-Archive: http://lists.mysql.com/mysql/227731
Message-Id: <4FE46D5D.7070705@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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