List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:June 22 2012 1:04pm
Subject:Re: Need Query Help
View as plain text  
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


Thread
Need Query HelpAnupam Karmarkar20 Jun
  • RE: Need Query HelpRick James20 Jun
    • Re: Need Query HelpAnupam Karmarkar22 Jun
      • Re: Need Query HelpShawn Green22 Jun