SQL LAG and LEAD Functions

    LAG and LEAD analytic functions came with SQL Server 2012. With these two functions you can  access data from the subsequent rows along with the data from the current row? Before SQL Server 2012, we used to do that by joining to the same table.

Let's start with LAG() function, you can access data from a previous row in the same result set.

LAG (scalar exp [,offset] [,default]) OVER ([partition clause] order by clause)

• [OffSet] This is the number of rows back from the current row. It can't be negative
• [Default] is the default value if there is no data.

These functions are very useful when you need them, Let's say we have our inventory table. Table contains all products. 

 

As you can see, we have two power cables in the table. We stopped carrying one, we want our query to show the older power cables price.

As you can see, we used to sell the power cable for $2.50 after we change our supplier, we start to sell it for $2.00 We didn't need to join to Products table to get the previous price.

---

LEAD function works exactly the same way, it gives us access to data in forward rows.

LEAD (scalar exp [,offset] [,default]) OVER ([partition clause] order by clause)

[OffSet] This is the number of rows forward from the current row. It can't be negative

[Default] is the default value if there is no data.

Let's say we received new USB Cables from the new supplier, There is a price difference between the current USB Cable and the new one. We like to see how much we are going to save/loose when we start to sell the new USB cables.

Our query is very similar, Only change is in the ORDER BY clause. Let's look at the results..

You can see the current USB Cable, we are selling it for $4 and new one will be cheaper. You can also see the Power Cable price change too.