How to refer to previous row in SQL 2005

In this post I will describe simple mechanism allowing to refer back to previous row in SQL Server 2005/2008 database table.  

For the purpose of this article let’s create a table dbo.AgentLog:  

create table dbo.AgentLog (
	 AgentID	int
	,LogInTime	datetime
	,LogOutTime	datetime
	)
go

insert dbo.AgentLog
select 1, '2010-09-08 09:40:00.000', '2010-09-08 10:14:00.000'
union all
select 1, '2010-09-08 10:35:00.000', '2010-09-08 12:04:00.000'
union all select 1, '2010-09-08 13:11:00.000', '2010-09-08 14:24:00.000'
union all select 1, '2010-09-08 14:35:00.000', '2010-09-08 16:08:00.000'
union all select 2, '2010-09-08 09:00:00.000', '2010-09-08 09:23:00.000'
union all select 2, '2010-09-08 10:12:00.000', '2010-09-08 12:05:00.000'
union all select 2, '2010-09-08 13:12:00.000', '2010-09-08 15:07:00.000'
union all select 2, '2010-09-08 15:10:00.000', '2010-09-08 16:25:00.000'
go

Our table will look like this:  

AgentID LogInTime LogOutTime
----------- ----------------------- -----------------------
1           2010-09-08 09:40:00.000 2010-09-08 10:14:00.000
1           2010-09-08 10:35:00.000 2010-09-08 12:04:00.000
1           2010-09-08 13:11:00.000 2010-09-08 14:24:00.000
1           2010-09-08 14:35:00.000 2010-09-08 16:08:00.000
2           2010-09-08 09:00:00.000 2010-09-08 09:23:00.000
2           2010-09-08 10:12:00.000 2010-09-08 12:05:00.000
2           2010-09-08 13:12:00.000 2010-09-08 15:07:00.000
2           2010-09-08 15:10:00.000 2010-09-08 16:25:00.000

The task is to for each agent list all breaks the person made and its length. To do this I need to somehow refer back to previous row to calculate difference in minutes between login and previous logout.  

To accomplish the task I will use two features new to SQL Server 2005 (when compared to SQL Server 2000). These are CTE (Common Table Expression) and Row_Number() function.  

First one in essence can be thought as variable table of results from SELECT statement. This data set only exists in current scope.  

Row_Number() is a SQL Server built-in function that adds extra identity column to data set retrieved with SELECT statement.  

SELECT
     Row_Number() over (partition by AgentID order by LogInTime) AS RowID
    ,*
FROM dbo.AgentLog

Row_Number() takes partition by argument, which tells function that you want unique row numbers against specific column. If this isn’t provided, SQL Server will just number all rows on one-by-one basis. Combining CTE and Row_Number() we can now get the following: 

 ;with AgentLogCTE as
(select Row_Number() over (partition by AgentID order by LogInTime) as RowID, * from dbo.AgentLog )

This CTE then contains following information:

RowID                AgentID     LogInTime               LogOutTime
-------------------- ----------- ----------------------- -----------------------
1                    1           2010-09-08 09:40:00.000 2010-09-08 10:14:00.000
2                    1           2010-09-08 10:35:00.000 2010-09-08 12:04:00.000
3                    1           2010-09-08 13:11:00.000 2010-09-08 14:24:00.000
4                    1           2010-09-08 14:35:00.000 2010-09-08 16:08:00.000
1                    2           2010-09-08 09:00:00.000 2010-09-08 09:23:00.000
2                    2           2010-09-08 10:12:00.000 2010-09-08 12:05:00.000
3                    2           2010-09-08 13:12:00.000 2010-09-08 15:07:00.000
4                    2           2010-09-08 15:10:00.000 2010-09-08 16:25:00.000

Now what we need to do is just join this table to itself in some clever way, linking current row to previous one:

select
    t1.AgentID
   ,t2.LogOutTime as PrevLogout
   ,t1.LogInTime as Login
   ,convert(varchar, t1.LogInTime - t2.LogOutTime, 108) as BreakTime
from AgentLogCTE t1
   join AgentLogCTE t2
      on t2.RowID = (t1.RowID-1) and t1.AgentID = t2.AgentID
go

And this is it! Resultset looks like the following:

AgentID     PrevLogout              Login                   BreakTime
----------- ----------------------- ----------------------- ------------------------------
1           2010-09-08 10:14:00.000 2010-09-08 10:35:00.000 00:21:00
1           2010-09-08 12:04:00.000 2010-09-08 13:11:00.000 01:07:00
1           2010-09-08 14:24:00.000 2010-09-08 14:35:00.000 00:11:00
2           2010-09-08 09:23:00.000 2010-09-08 10:12:00.000 00:49:00
2           2010-09-08 12:05:00.000 2010-09-08 13:12:00.000 01:07:00
2           2010-09-08 15:07:00.000 2010-09-08 15:10:00.000 00:03:00

Whole SQL script explained in this article can be obtained from here.