SQL Server 2012 LEAD and LAG

LEAD and LAG are two analytical functions that have been introduced in SQL Server 2012. Let’s quickly see the usage of these functions with an example.

Sometimes you may need to display the previous or next row values for a column in the same row. In earlier versions, we had to use a Self join to do this. But from SQL Server 2012, we can make use of the analytical functions – LEAD and LAG

Consider the following set of data.

create table #sales (sales_id int identity(1,1), sales_date datetime, product_id int, qty int, sales_amount decimal(12))
insert into #sales(sales_date , product_id , qty,sales_amount)
select '20001111',1,12,48882 union all
select '20001112',1,33,65544 union all
select '20001113',1,9, 31289 union all
select '20001114',1,22,58860

Suppose you want to show the sales amount for a day as well as sales amount for the next day. Use the LEAD function as shown below

select  product_id,sales_date,sales_amount, lead(sales_amount) over (order by sales_date) as next_sales_amount from #sales

Similarly, if you want to show the sales amount for a day as well as sales amount for the previous day, use the LAG function as shown below

select  product_id,sales_date,sales_amount, lag(sales_amount) over (order by sales_date) as previous_sales_amount from #sales

Note that like other windows functions row_number(), rank(), etc, both LEAD and LAG functions need Order by clause. It determines the order of rows so that SQL Server picks previous and next rows easily.

The following queries show values based on qty column

select  product_id,sales_date,sales_amount, lead(sales_amount) over (order by qty) as next_sales_amount from #sales

select  product_id,sales_date,sales_amount, lag(sales_amount) over (order by qty) as next_sales_amount from #sales

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan