query next row of data

[cheryl27284]

I need to figure out the duration of time someone logged off the phone for different reasons. Each record has a logoff and a Logon. In order to determine the duration I have to look at the very next record to see what the logon was. If someone logs off for a reason (reasons are numeric 1-5)the record will tell me the time they logged off but not the duration. The next record tells me when they logged back in. I have to compare the very next record. How do I do this?

table looks like below:
EmployeeID reason logon logoff
444 2 10/11/2005 12:00 AM 10/11/2005 12:05 AM
444 1 10/11/2005 12:10 AM 10/11/2005 12:30

 

[gmmastros]

I'm not completely sure I understand your requirements, but give this a shot. It may be what you are looking for.

Select A.EmployeeId,
       A.Logoff,
       B.Logon
From   <Table> As A
       Inner Join <Table> as B On A.EmployeeId = B.EmployeeId
Where  A.Reason = 2
       And B.Reason = 1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cheryl27284]

Oh I wish it were that easy.. The problem is that the employees can logoff and logon many times so each employee has several records a day. I need to compare the first record to the very next record in line. Becuase the two records will always go together. First record they logoff at this time. The next record tells me when they logged back on.

 

[gmmastros]

There is a solution for this. For me to help you better, can you post some more data? 10 or 15 records would suffice. Also, should I assume that Reason has nothing got nothing to do with this query?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cheryl27284]

Thank You so much! Here are 6 records. Each reason is an indicator as to why a employee logs out. So I want to know how long employee '40019' logged out for reason '4' I would have to compare the reason 4 record to the very next one to see when he logged back in. So from looking at the records below, he logged out reason 4 for 5 minutes.


EmployeeID Login Logout Reason
40019 11/10/2005 2:00:00 PM 11/10/2005 2:15:00 PM 4
40019 11/10/2005 2:20:00 PM 11/10/2005 2:25:00 PM 1
40019 11/10/2005 2:35:00 PM 11/10/2005 5:35:00 PM 2
40019 11/10/2005 5:36:00 PM 11/10/2005 5:38:00 PM 3
40019 11/10/2005 5:40:00 PM 11/10/2005 5:45:00 PM 4
40019 11/10/2005 6:00:00 PM 11/10/2005 6:35:00 PM 2

 

[gmmastros]

I suggest you create a stored procedure so that you can use Table Variables. I use a table variable here so that I can introduce an identity column which helps with the self join.

Create Procedure GetLoggedOffTimes
As
Set NOCOUNT ON
Declare @Temp 
Table	(RowId Integer Identity(1,1), 
		EmployeeId Integer, 
		ReasonId Integer, 
		Logon SmallDateTime, 
		Logout SmallDateTime)

Insert Into @Temp(EmployeeId, ReasonId, Logon, Logout)
Select	EmployeeId, ReasonId, Logon, Logout
From	<TableName>
Order By EmployeeId, Logon

Select	A.EmployeeId, A.ReasonId, A.Logout, B.Logon, DateDiff(Minute, A.Logout, B.Logon) As DurationInMinutes
From	@Temp A
		Inner Join @Temp B On A.EmployeeId = B.EmployeeId And A.RowId = B.RowId - 1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cheryl27284]

Hey thanks, I will try that