LEAD and LAG Analytic functions in SQL Server 2012

LEAD and LAG Analytic functions in SQL Server 2012

LEAD and LAG functions were highly awaited and demanded function in SQL Server and finally it is now introduced in SQL Server 2012. I really like some of new functions introduced in SQL Server 2012 which I have listed in my earlier blog post. If you wish, you can access those articles from the links given below:

New Datetime Functions in SQL Server 2012 (Click Here)

New PARSE, TRY_PARSE & TRY_CONVERT conversion functions in SQL Server 2012 (Click Here)

New LAST_VALUE and FIRST_VALUE Analytic function in SQL Server 2012 (Click Here)

CUME_DIST and PERCENT_RANK new analytic functions in SQL Server 2012 (Click Here)

Well, now moving on!!!

Do you remember when you wanted to get previous and next value of particular column for particular row? I do remember it and I know I have used self-join, CTE, cursor , loop and many different type of logical solution but now our life become little easier with two new analytic functions in SQL Server which are known as LEAD & LAG.

LEAD: This function accesses the data from next row without any help of self-join or CTE.

LAG: This function accesses the data from previous row without any help of self-join or CTE.

Though, definition of these functions is short but use of these functions is really big and save you from developing customized logic many time. Let us understand this by small example here.

[sourcecode language=”sql”]
IF OBJECT_ID(‘tempdb..#AnalyticFunctionCheck’) IS NOT NULL
DROP TABLE #AnalyticFunctionCheck

CREATE TABLE #AnalyticFunctionCheck
 (
 ID INT IDENTITY(1,1)
 ,ClientID VARCHAR(10)
 ,TestName VARCHAR(20)
 ,TotalSample INT
 )

INSERT INTO #AnalyticFunctionCheck
SELECT ‘CHEM02′,’VOCMS GROUP 1’,4 UNION ALL
SELECT ‘CHEM02′,’SVOC GROUP 1’,6 UNION ALL
SELECT ‘CHEM02′,’SVOC STARS’,12 UNION ALL
SELECT ‘CHEM02′,’PESTICIDE’,4 UNION ALL
SELECT ‘SHAW01′,’PESTICIDE’,10 UNION ALL
SELECT ‘SHAW01′,’SVOC STARS’,3 UNION ALL
SELECT ‘SHAW01′,’SVOC GROUP 1’,9 UNION ALL
SELECT ‘EPAW01′,’SVOC GROUP 1’,14

SELECT
*
FROM
#AnalyticFunctionCheck

SELECT
*
,LAG(ID, 1) OVER (ORDER BY ID) AS PreviousQuota
,LEAD(ID, 1) OVER (ORDER BY ID) AS PreviousQuota
FROM
#AnalyticFunctionCheck

SELECT
*
,LAG(ID, 2) OVER (ORDER BY ID) AS PreviousQuota
,LEAD(ID, 2) OVER (ORDER BY ID) AS PreviousQuota
FROM
#AnalyticFunctionCheck
[/sourcecode]

After executing above given 3 queries, you will find result set like this:

First result set is simple “SELECT *” query on the temp table we created.

Second result set had offset “1” (refer LAG(ID, 1)). 1 is a default offset and it provides previous row if it is LAG function and next row if it is LEAD function.

Third result set returns ID of 2nd previous/Next row as we have provided offset “2” (refer LAG(ID, 2)) based on the function LEAD/LAG provided.

If function will not find previous/next value, it will return NULL by default.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

4 thoughts on “LEAD and LAG Analytic functions in SQL Server 2012”

Comments are closed.