LAG() function syntax with an example in SQL Server

LAG() 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: 

·        LAG provides access to a row at a given physical offset that comes before the current row.

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

Example:

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

USE AdventureWorks2008R2;

GO

SELECT BusinessEntityID, YEAR (QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LAG (SalesQuota, 1, 0) OVER (ORDER BY YEAR (QuotaDate)) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory

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

Result:

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

Here, there is no lag value available for the first row, the default of zero (0) is returned.

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