#Sql server lead and lag how to

The CTE is used in the main query to create the two derived tables, LogOns and LogOffs.The Row_Number() function enumerates rows in the order specified in the ORDER BY clause in the OVER() clause.HereвЂ™s the data produced by the CTE with its RowNumber column: This is just the UserActivity table with an additional Row_Number() column-the enumeration we need in order by User and ActivityTime. Find a CTE called UserActivityWRowNum in the query.IвЂ™ll show you the query now and then discuss it in detail below. To implement this solution, weвЂ™ll use the Row_Number() function, the OVER() clause of a SELECT statement, a CTE and an inner join. To make sure matched rows are for the same user and activity date, we will also include those two fields in the join condition when we write the query. We can then join row x in LogOn to row x-1 in LogOff. To match up a row with its previous row, we can enumerate the rows of the UserActivity table and include that enumeration in the two derived tables (rows in LogOn will all have odd row numbers and rows in LogOff will all have even row numbers). Next, weвЂ™ll need to join them so that a logon row is paired up with its logoff row. WeвЂ™ll start by creating two derived tables-one holding only the logon rows in UserActivity and the other holding only the logoff rows.

#Sql server lead and lag how to

LetвЂ™s see how to do it.ДЄ Solution Using SQLServer 2005, 2008 or 2008 R2 This business of accessing previous or next rows requires some minor acrobatics in SQL Server 2008 R2 and previous versions. Remember, SQL queries perform operations on a row-by-row basis. ThatвЂ™s the obstacle to be surmounted, here. To calculate the idle time, weвЂ™ll need to find the difference between the time in a logon row and the time in the previous row. Note that blocks of idle time that span two days will not be used. Take a moment to verify that, for user 1 on (the first 6 rows of the UserActivity table), there are two blocks of idle time one is from 10:45 to 1:15 (2 ВЅ hr., or 150 min.), and the other is from 3:30 to 4:15 (45 min). , (2, 'LogOff', CAST(' 14:45' AS datetime))ДЇrom this table we need to calculate the idle time per user per day-the time between a logon and the previous logoff for the same user on the same day. INSERT dbo.UserActivity (, Activity, ActivityTime) Create the UserActivity in the dbo schema When youвЂ™re done with this article, you can simply drop the UserManagement database to clean things up. The script creates a database called UserManagement and then it creates and populates a table called UserActivity. IвЂ™ll describe the problem I ran into and show you how to implement a solution using both SQL Server versions 2005 вЂ“ 2008 R2 and SQL Server 2012.Д«efore I get to the problem, hereвЂ™s a script you can run to set up an environment for this example in case you want to follow along by doing. In 2012 and higher, there are two functions, Lag() and Lead(), that greatly simplify the process. In SQL Server versions prior to 2012, you need to perform a join using a row enumerator to match up rows with previous or next rows. The problem is that SQL queries perform operations on a row-by-row basis accessing data on different rows at the same time requires the query to do some extra work. I recently came across a problem that required having to perform a calculation in a query that involved a value in the current row and a value in the previous row.