DevHeads.net

Need Query Help

By Anupam Karmarkar at 06/20/2012 - 05:38

Hi All,

I need query help for following table struture, where we need to calculate login duration of that employee for give period.

Example table

EmployeeID     LoginTime                       LogoutTIme

101                 2012-05-01 10:00:00     2012-05-01 12:30:00
102                 2012-04-31 23:00:00      2012-05-02 05:00:00 

103                 2012-05-01 14:00:00      NULL   
104                 2012-05-02 00:10:00      2012-05-02 05:00:00

I tried to fit all scenario in above table, Consider NULL as yet to logout

How would i calcuate Employee and it Login duration for period say from 2012-05-01 08:00:00 to 2012-05-01 22:00:00

--Anupam

Comments

RE: Need Query Help

By Rick James at 06/20/2012 - 13:22

<a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff" title="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff">http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#func...</a>
and SEC_TO_TIME()/3600

Re: Need Query Help

By Anupam Karmarkar at 06/22/2012 - 00:18

Thanks Rick for your reply,

Here i am asking about logic to perpare query or whole query itself.

--Anupam

<a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff" title="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff">http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#func...</a>
and SEC_TO_TIME()/3600

Re: Need Query Help

By Shawn Green (MySQL) at 06/22/2012 - 09:04

On 6/22/2012 12:18 AM, Anupam Karmarkar wrote:
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,