The LEAD function is applied to each partition separately and computation restarts for each partition. The PARTITION BY clause is specified to partition the rows in the result set by sales territory.

The following example uses the LEAD function to compare year-to-date sales between employees. BusinessEntityID SalesYear CurrentQuota NextQuota WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005','2006') LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, Notice that because there is no lead value available for the last row, the default of zero (0) is returned. The query uses the LEAD function to return the difference in sales quotas for a specific employee over subsequent years. For more information, see Deterministic and Nondeterministic Functions. NULL is returned if scalar_expression is nullable or default is set to NULL. The data type of the specified scalar_expression. For more information, see OVER Clause (Transact-SQL). When partition_by_clause is specified, it determines the order of the data in each partition. order_by_clause determines the order of the data before the function is applied. If not specified, the function treats all rows of the query result set as a single group. Partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. default must be type-compatible with scalar_expression.

default can be a column, subquery, or other expression, but it cannot be an analytic function.

If a default value is not specified, NULL is returned. The value to return when offset is beyond the scope of the partition.

offset cannot be a negative value or an analytic function. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. The number of rows forward from the current row from which to obtain a value. scalar_expression cannot be an analytic function. It is an expression of any type that returns a single (scalar) value. The value to be returned based on the specified offset. To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Sql lag and lead examples