Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted November 20, 2014

Lead and Lag Functions in SQL Server 2012

By Arshad Ali

Introduction

Have you ever been in a situation where you needed to write a query that needed to do comparisons or access data from the subsequent rows along with the data from the current row? This article discusses different ways to write these types of queries and more specifically examines LEAD and LAG analytics functions, which were introduced with SQL Server 2012, and helps you understand how leveraging these functions can aid you in such situations.

Accessing Prior or Subsequent Rows

SQL Server 2012 introduced LAG and LEAD functions for accessing prior or subsequent rows along with the current row but before we go into the details of these functions, let me explain how you can write these queries in earlier versions of SQL Server.

Let’s first create a table and load some sample data with the script below. This table contains customer information along with when a specific plan for the customer was started, assuming when a new plan starts, the older one gets ended automatically.

DECLARE   @CustomerPlan TABLE
  (
       CustomerCode VARCHAR(10),
         PlanCode   VARCHAR(10),
       StartDate  DATE
    )
INSERT INTO @CustomerPlan VALUES ('C00001', 'P00001', '1-Sep-2014')
INSERT INTO @CustomerPlan VALUES ('C00001', 'P00002', '1-Oct-2014')
INSERT INTO @CustomerPlan VALUES ('C00001', 'P00003', '10-Oct-2014')
INSERT INTO @CustomerPlan VALUES ('C00001', 'P00004', '25-Oct-2014')
INSERT INTO @CustomerPlan VALUES ('C00002', 'P00001', '1-Oct-2014')
INSERT INTO @CustomerPlan VALUES ('C00002', 'P00002', '1-Nov-2014')
 
SELECT * FROM   @CustomerPlan;

Query Results

You can use Common Table Expression (CTE) along with the ROW_NUMBER ranking function to access subsequent rows in the same result set. For example, for a given customer I want to know the expiration date for the current plan based on the activation date of the next plan. Basically, when a new plan is started the previous plan is automatically ended and hence the end date for a previous plan is the start date minus one day of the next plan:

WITH   CTE as 
(
        SELECT RN = ROW_NUMBER() OVER (PARTITION BY   CustomerCode ORDER BY   StartDate ASC),   * 
        FROM @CustomerPlan
)
SELECT
        [Current   Row].*, 
        ISNULL(DATEADD(DAY, -1, [Next Row].StartDate), '31-Dec-2099') AS EndDate
FROM   CTE [Current Row]
        LEFT JOIN CTE [Next   Row] ON [Current Row].CustomerCode   = [Next Row].CustomerCode   AND  [Next Row].RN   = [Current Row].RN   + 1
ORDER BY [Current Row].CustomerCode, [Current Row].RN;

Query Results

In the image above, you can see the plan P00002 of customer C00001 starts on 1st October 2014 and hence the end date for plan P00001 is the 30th September 2014 (1st October 2014 minus one day), likewise P00003 starts on 10th October and hence the end date for P00002 ends on 9th October 2014.

LEAD Function in SQL Server 2012

Though it was very much possible to write queries as shown above using CTE and ranking function to access prior or subsequent rows in earlier versions of SQL Server, SQL Server 2012 has simplified it further.

SQL Server 2012 introduced LEAD analytic function to access the subsequent row (or columns from the subsequent row) without using self-join or CTE or ranking function.

There are three main parameters for the LEAD function along with the OVER clause, which works in the same way as it works with ranking functions:

  • With the first parameter, you can specify a scalar expression or column name whose value from the subsequent row is to be returned.
  • With the second parameter, you can specify an offset to access not only the next immediate row but any row after the current row. For example, its default value of 1 accesses the next immediate row whereas a value of 3 accesses the third row from the current row.
  • With the third parameter, you can specify the default value to be returned in case the returned value is NULL.

The script below is a re-write of the above script (which uses CTE and ranking function) using the LEAD function, which gives the same result as above, but as you can see this query is more simplified and does not uses self-join.

SELECT *, 
DATEADD(DAY, -1, LEAD(StartDate, 1,'01-Jan-2100') 
        OVER (PARTITION BY   CustomerCode ORDER BY   StartDate ASC))   AS EndDate
FROM   @CustomerPlan

Query Results

LAG Function in SQL Server 2012

Like the LEAD function, LAG is another analytic function introduced in SQL Server 2012 that has the same three parameters as the LEAD function along with the OVER clause, which works in same way as it works with ranking functions. But unlike the LEAD function, which allows accessing subsequent rows, the LAG function allows accessing previous rows from the same result set, again without using self-join. With the second offset parameter, you can specify the physical offset that comes before the current row.

If you look at the structure of the table we created above, each row contains a customer’s current plan. With the help of the LAG function, we will find out the previous (plan from the last row) as shown in the script below:

SELECT   CustomerCode, PlanCode AS CurrentPlanCode,
LAG(PlanCode, 1, 'NA') 
        OVER (PARTITION BY   CustomerCode ORDER BY   StartDate ASC)   AS LastPlan
FROM   @CustomerPlan;

If you notice in the figure below, the second row contains the P00002 as the current plan and P00001 as the last plan (coming the immediate previous row); likewise the third row contains P00003 as the current plan and P00002 as the last plan (again coming from the immediate previous row).

Query Results

LEAD and LAG functions are flexible to let you specify the number of rows to move backward or forward from the current row with the offset parameter. For example, as you can see in the script below I have specified 2 as offset with the LAG function and hence for the third row where the current plan is P00003, the last plan is P00001 (as it goes two rows back to pick up the value).

SELECT   CustomerCode, PlanCode AS CurrentPlanCode,
LAG(PlanCode, 2, 'NA') 
        OVER (PARTITION BY   CustomerCode ORDER BY   StartDate ASC)   AS LastPlan
FROM   @CustomerPlan;

Query Results

Conclusion

In this article I discussed how you can use CTE and ranking function to access or query data from previous or subsequent rows. Then I also talked about how to leverage LEAD and LAG analytics functions, introduced with SQL Server 2012, to achieve the same without writing  self-join query using CTE and ranking function.

Resources

LEAD (Transact-SQL)

LAG (Transact-SQL)

See all articles by Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM