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 Denali, CTP3 for SQL Server 2012 developers.
What SQL Server Lead() function does is returning simply the next Nth row value in an order.
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
Here is another similar example t-sql code that developers can execute on sampel 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
|