SQL Server 2012 Analytical Functions: Lead and Lag

SQL Server 2012 supports many T-SQL analytic functions which are listed below:

• LEAD: Accesses data from a subsequent row in the same result set without the use of a self-join.
• LAG: Accesses data from a previous row in the same result set without the use of a self-join.
• CUME_DIST: Calculates the cumulative distribution of a value in a group of values. In other words, CUME_DIST computes the relative position of a specifiedvalue in a group of values
• FIRST_VALUE: Returns the first value in an ordered set of values.
• LAST_VALUE: Returns the last value in an ordered set of values.
• PERCENTILE_CONT: Calculates a percentile based on a continuous distribution of the column.
• PERCENTILE_DISC: Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset.
• PERCENT_RANK: Calculates the relative rank of a row within a group of rows.

Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, they can return multiple rows for each group. You can use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.

In this article, we are going to focus only on Lead and Lag functions.

Lead function accesses data from a subsequent row and Lag function accesses from previous row in the same result set without the use of a self-join.

Syntax:

LEAD | Lag ( scalar_expression [ ,offset ] , [ default ] )
    OVER ( [ partition_by_clause ] order_by_clause )

scalar_expression is the value to be returned based on the specified offset. Offset specify the number of rows forward/backward from the current row from which to obtain a value. Default is the value to return when scalar_expression at offset is NULL.

Now lets explorer Lead function with an example:

Problem: Compare values between current year, previors year, and next year.

---------------------

-- Prepare Test Data

---------------------

DECLARE @TestData TABLE (Period DATE, Amount INT

INSERT @TestData

SELECT '2006-12-31' Period, 1000 Amount UNION

SELECT '2007-12-31' Period, 3000 Amount UNION

SELECT '2008-12-31' Period, 5000 Amount UNION

SELECT '2009-12-31' Period, 4000 Amount UNION

SELECT '2010-12-31' Period, 7000 Amount UNION

SELECT '2011-12-31' Period, 6000 Amount UNION

SELECT '2012-12-31' Period, 8000 Amount

 

----------------------------------------------------------

-- Fetch current year, previous year, and next year data

----------------------------------------------------------

SELECT Period
,Amount AS CurrentAmount

,LAG(Amount, 1, 0) OVER (ORDER BY Period) AS PreviousAmount

,LEAD(Amount, 1, 0) OVER (ORDER BY Period) AS NextAmount

FROM @TestData

 

 

 

 

 

 

Note that in the above we have set the default vaule 0 for Lead and Lag functions so we are getting zero (0) instead of NULL. Also we have set the offset to 1 for lead and lag function so we are getting only subsequent  and previous values. Offset can be set as per requirement but in most of the cases we use 1.