Posts Tagged by CTE

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:  

Our table will look like this:  

The task is to for each agent list all breaks the person made and its […]

Our table will look like this:  

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.  

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:

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

And this is it! Resultset looks like the following:

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