*** How to Query a Timeclock Table for Log Out Times

Hello Experts,
I have a Timeclock table for all my employees, and I need to find out how long each employee was logged out for (NOT including the end-of-day LogOut when they go home).

TimeClock Table:

Employee       Log_In_Time                           Log_Out_Time
64                2010-03-01 17:06:29.000           2010-03-01 19:24:06.000
64                2010-03-01 19:44:05.000           2010-03-01 20:49:30.000
209              2010-03-01 17:50:59.000           2010-03-01 19:06:02.000
209              2010-03-01 19:18:19.000           2010-03-01 20:16:04.000
209              2010-03-01 20:35:44.000           2010-03-01 20:51:08.000
787              2010-03-01 17:31:34.000           2010-03-01 17:38:23.000
787              2010-03-01 17:38:32.000           2010-03-01 20:17:16.000
787              2010-03-01 20:33:44.000           2010-03-01 20:44:13.000

So basically I want to know, for each Employee, the time between their last Log_Out_Time and their next Log_In_Time .... this tells me how long they were looged out.

This logic should only apply to SAME DAY records... because the last logout record for an employee implies that it is the end of their shift and they go home.  So your logic cannot cross multiple dates.

So in the above table, Employee 209 logged out for 12 minutes (19:06 to 19:18) and for 19 minutes (20:16 to 20:35) on 3/1/10.  How can this be coded?  Ideally, your code will work on SQL 2005 as well as 2000 if possible.

My sample table includes on date 3/1/10, but the real table contains every day for the last 5 years.

Thanks for your help!!!

dunkin1969Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
The following appears to be working in SQL Server


SELECT t1.Employee, MAX(t2.Log_Out_Time) AS LastLogOut, t1.Log_In_Time, 
	DATEDIFF(s, MAX(t2.Log_Out_Time), t1.Log_In_Time) / 60.0 AS LoggedOutMinutes
FROM TimeClock t1 LEFT JOIN
	TimeClock t2 ON t1.Employee = t2.Employee AND t2.Log_Out_Time < t1.Log_In_Time
GROUP BY t1.Employee, t1.Log_In_Time
HAVING CONVERT(varchar, MAX(t2.Log_Out_Time), 102) = CONVERT(varchar, t1.Log_In_Time, 102)
ORDER BY t1.Employee, t1.Log_In_Time

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dunkin1969Author Commented:
Thanks Matthew!
This looks like it works perfectly.  Let me now run it against a full month's worth of data.
dunkin1969Author Commented:
Great concise code.  Thanks!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.