SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server 2012 Download and T-SQL Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



SQL LEAD() Function in SQL Server 2012 for Calculating Next Value

SQL LEAD() function is one of the recent enhancements among SQL Analytic Functions introduced with SQL Server 2012 (aka Denali, CTP3) for developers. What SQL Server Lead() function does is returning simply the next Nth row value in an order. Even only this enables SQL Lead() function to find gaps in number sequences. The order of the rows can be grouped by using the Partition By clause for powering the SQL Lead() function.

Here is the SQL Analytic LEAD() function syntax for T-SQL developers

LEAD (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)

The Lead() function input offset argument determines the number of rows in order that the SQL Engine will read from. Offset input parameter is optional. If nothing is provided, then the default value 1 will be used in LEAD() function

The default argument sets the value which will be returned if SQL Lead() function returns nothing.

Here is our first SQL Server Lead() function example.
If you look at the returned result set of the below SQL query, you will realize that all data from AnalyticFunctions table is listed. Besides the value of the next row when ordered by Id column with the same Category value is displayed in NextValue column.

You see, the Lead() function offset argument is 1 as default value. This returns the next row value.
And the Lead() function default argument is NULL. Since the last row for each category does not have a following row, the SQL LEAD() function cannot calculated the NextValue column, and returns the default NULL value.

SELECT
 *,
 LEAD(Value, 1, NULL) OVER (Partition By Category ORDER BY Id) AS NextValue
FROM AnalyticFunctions

TSQL LEAD() function in SQL Server 2012 for next value calculation

Here is another similar example t-sql code that developers can execute on sample database AdventureWorks2008R2

This SELECT query lists Customer info, Sales Order of the customer and the Total amount. Besides all these information, using new SQL Server Lead() analytic function, the total amount of the next sales to the related customer is also displayed as an additional column.

select
 CustomerID, OrderDate, TotalDue,
 LEAD(TotalDue, 1, NULL) OVER (Partition By CustomerID ORDER BY OrderDate) NextOrder_TotalDue
from Sales.SalesOrderHeader

SQL Server LEAD() analytic function sample

As I mentioned before Lead() function can be also used to detect and list gaps in numeric sequences in a SQL Server database table column. Here is a short SQL script where SQL analytic function Lead() is used to find missing numbers in the numeric sequence.

with cte as (
 select
 OrderId, Lead(OrderId) over (order by OrderId) NextOrder
 from Orders
)
select
 OrderId, NextOrder
from cte
where OrderId < NextOrder - 1






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems