SQL Server Performance Forum – Threads Archive

Joining a table on itself to find a closing record

Hey everybody, I’m having fits trying to create a query that joins a table upon itself. I need to calculate how much time a person has spent at work, at lunch, at break, how much was billable and how much wasn’t. The base table is a list of timeclock events, it looks like this: ID EmployeeID Eventid Timestamp
— ———- ——- ———————–
1 44 0 2005-01-16 07:26:59.903
2 44 1 2005-01-16 07:29:57.687 bathroom break
3 44 -2 2005-01-16 07:32:44.621 resumed work
4 44 3 2005-01-16 08:18:30.780 10 minute coffee break
5 44 -2 2005-01-16 08:20:38.500 resumed working
…
11 44 -1 2005-01-16 14:19:21.099 Logged out
I’d like to construct a query that can match events together like login and logout times, began a break and returned from break. This way I’d be able to calculate the times for each kind of event and make everyone happy without using a cursor. SELECT A.TimeStamp, B.Timestamp,
DATEDIFF(ss,A.TimeStamp,B.Timestamp) as Total
FROM TimeClockTable A INNER JOIN
TimeClockTable B ON A.TimeClockTable = B.TimeClockTable
AND A.EventID = (the very next EventID that’s equal to -2)
WHERE … The second part of the join is where I’m having trouble. How do I construct the join so I’m getting the *next* ‘resuming work’ event? One other note, I can count on the ID field to be an identity field, but it will not be in sequential order. They will be ascending, these records will be mixed in with records from 100+ other employees. Thanks for your help, – Dan Broderick

---

Refer this threadhttp://sql-server-performance.com/forum/topic.asp?TOPIC_ID=6247 Madhivanan

---

]]>