LEAD() function syntax with an example in SQL Server

LEAD() function syntax with an example in SQL Server 

Introduction: 

In my previous posts we have seen what is Transact-Sql in sql server and What are new top features of T-Sql in server. This is one of the best sql server interview Questions and answers. Watch more ASP.NET, C#.NET, Jquery, Sql Server, HTML5, HTML, CSS, JQUERY UI Interview questions from here. 

Description:  

·        LEAD provides access to a row at a given physical offset that follows the current row.

·        This function is mainly used in a SELECT statement to compare values in the current row with values in a following row.

Example:

The following example uses the LEAD function to return the difference in sales quotas for a specific employee over subsequent years.

USE AdventureWorks2008R2;

GO

SELECT BusinessEntityID, YEAR (QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LEAD (SalesQuota, 1, 0) OVER (ORDER BY YEAR (QuotaDate)) AS NextQuota

FROM Sales.SalesPersonQuotaHistory

WHERE BusinessEntityID = 275 and YEAR (QuotaDate) IN ('2005','2006');

          Result:

BusinessEntityID	SalesYear	CurrentQuota	NextQuota
275	2005	367000.00	556000.00
275	2005	556000.00	502000.00
275	2006	502000.00	550000.00
275	2006	550000.00	1429000.00
275	2006	1429000.00	1324000.00
275	2006	1324000.00	0.00

            

If you have any queries or suggestions, please feel free to ask in comments section.