SQL Server 2012 introduced new analytical function LEAD() and LAG().

These functions accesses data from nth next row and nth previous row in the same result set without the use of a self-join:

  • LEAD(): It is used to access data from nth next row in the same result set without the use of a self-join.
  • LAG(): It is used to access data from nth previous row in the same result set without the use of a self-join.

Syntax

  1. LEAD (scalar_expression [,offset] [,default])
  2. OVER ( [ partition_by_clause ] order_by_clause )
  4. LAG (scalar_expression [,offset] [,default])
  5. OVER ( [ partition_by_clause ] order_by_clause )
Parameters
  • scalar_expression: Column name for which value to be accessed.
  • offset: Nth previous (for lag) or next (for lead) row to access the column.
  • defaul : Default value to display if nth row does not exist.

Example 1

  1. DECLARE @Test_table TABLE(
  2. Year INT, Sale INT
  3. )
  5. INSERT INTO @Test_table VALUES('2008',5000000)
  6. INSERT INTO @Test_table VALUES('2009',5500000)
  7. INSERT INTO @Test_table VALUES('2010',5250000)
  8. INSERT INTO @Test_table VALUES('2011',6025000)
  9. INSERT INTO @Test_table VALUES('2012',6200000)
  11. SELECT Year, Sale
  12. , LEAD(Sale) OVER (ORDER BY Year) AS [Next Year Sale]
  13. , LAG(Sale) OVER (ORDER BY Year) AS [Prev Year Sale]
  14. , LEAD(Sale, 2) OVER (ORDER BY Year) AS [2nd Next Year Sale]
  15. , LAG(Sale, 2) OVER (ORDER BY Year) AS [2nd Prev Year Sale]
  16. , LEAD(Sale, 2, 0) OVER (ORDER BY Year) AS [2nd Next Year Sale]
  17. , LAG(Sale, 2, 0) OVER (ORDER BY Year) AS [2nd Prev Year Sale]
  18. FROM @Test_table
Output

output

Example 2 (With Partition By)
  1. DECLARE @Test_table TABLE(
  2. Year INT, Zone VARCHAR(10), Sale INT
  3. )
  4. INSERT INTO @Test_table VALUES('2009', 'East', 5500000)
  5. INSERT INTO @Test_table VALUES('2010', 'East', 5250000)
  6. INSERT INTO @Test_table VALUES('2011', 'East', 6025000)
  7. INSERT INTO @Test_table VALUES('2012', 'East', 6200000)
  8. INSERT INTO @Test_table VALUES('2009', 'West', 5200000)
  9. INSERT INTO @Test_table VALUES('2010', 'West', 5250000)
  10. INSERT INTO @Test_table VALUES('2011', 'West', 5525000)
  11. INSERT INTO @Test_table VALUES('2012', 'West', 5700000)
  12. INSERT INTO @Test_table VALUES('2009', 'North', 4700000)
  13. INSERT INTO @Test_table VALUES('2010', 'North', 4800000)
  14. INSERT INTO @Test_table VALUES('2011', 'North', 5000000)
  15. INSERT INTO @Test_table VALUES('2012', 'North', 5050000)
  16. INSERT INTO @Test_table VALUES('2009', 'South', 7200000)
  17. INSERT INTO @Test_table VALUES('2010', 'South', 7500000)
  18. INSERT INTO @Test_table VALUES('2011', 'South', 7800000)
  19. INSERT INTO @Test_table VALUES('2012', 'South', 8000000)
  21. SELECT Year, Sale
  22. , LEAD(Sale) OVER (PARTITION BY Zone ORDER BY Year) AS [Next Year Sale]
  23. , LAG(Sale) OVER (PARTITION BY Zone ORDER BY Year) AS [Prev Year Sale]
  24. , LEAD(Sale, 2) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Next Year Sale]
  25. , LAG(Sale, 2) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Prev Year Sale]
  26. , LEAD(Sale, 2, 0) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Next Year Sale]
  27. , LAG(Sale, 2, 0) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Prev Year Sale]
  28. FROM @Test_table
Output

output