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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

SQL LEAD Function in SQL Server 2012 to Calculate 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.

 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.

 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 (
 OrderId, Lead(OrderId) over (order by OrderId) NextOrder
 from Orders
 OrderId, NextOrder
from cte
where OrderId < NextOrder - 1

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.