Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server 2012 Functions - Lead and Lag


By:   |   Read Comments (11)   |   Related Tips: 1 | 2 | 3 | 4 | More > Functions - System

Problem

SQL Server 2012 introduces two new analytical functions, LEAD() and LAG(). In this tip we will be exploring these functions and how to use them.

Solution

These functions access data from a subsequent row (lead) and previous row (lag) in the same result set without the use of a self-join.

The syntax for the Lead and Lag functions is:

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

Let me explain using this example which creates table Test_table in database TestDB and inserts some data.

CREATE DATABASE [TestDB]
 
--Create testable to hold some data
CREATE TABLE [dbo].[Test_table](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [Department] [nchar](10) NOT NULL,
 [Code] [int] NOT NULL,
 CONSTRAINT [PK_Test_table] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--Insert some test data
insert into Test_table values('A',111)
insert into Test_table values('B',29)
insert into Test_table values('C',258)
insert into Test_table values('D',333)
insert into Test_table values('E',15)
insert into Test_table values('F',449)
insert into Test_table values('G',419)
insert into Test_table values('H',555)
insert into Test_table values('I',524)
insert into Test_table values('J',698)
insert into Test_table values('K',715)
insert into Test_table values('L',799)
insert into Test_table values('M',139)
insert into Test_table values('N',219)
insert into Test_table values('O',869)

Our table data will look like this:

Create Test_table on the databse TestDB

Now the query for lead value and lag value will be:

SELECT id,department,Code,
LEAD(Code,1) OVER (ORDER BY Code ) LeadValue,
LAG(Code,1) OVER (ORDER BY Code ) LagValue
FROM test_table

Now the query for leadvalue and lagvalue will be

In the above example, for the first row the Lead value is the value of the next row because the offset is set to 1. The Lag value is NULL because there were no previous rows.

Now if we change the Lead offset to 2 and Lag offset to 3 the output will be as follows:

If we change Lead offset to 2 and Lag offset to 3 the output will be:

One thing to note is that NULL values appear, because there are not values for the Lag or Lead.  To replace NULL values with zero add 0 in Lead\Lag function as shown below. 

SELECT id,department,Code,
LEAD(Code,2,0) OVER (ORDER BY Code ) LeadValue,
LAG(Code,3,0) OVER (ORDER BY Code ) LagValue
FFROM test_table
replace NULL with 0' add 0 in Lead\Lag function.
Next Steps


Last Update:





About the author





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, May 17, 2016 - 4:59:49 PM - varun Back To Top

 HI,

 

I have a requirement to create offset values for years, months, quarters and dates 

ex: 2016 = 0, 2015=1, 2014=2...etc and

2017= -1, 2018 = -2 and so on..

I can get the offset values but not quite sure how to map the same to use in the report so that it can be used as nyearsago , nmonthsago, ndaysago etc

Any Thoughts ?

The query used to get offset values are given below.

select top 10 Datediff(day, CAST(replace(convert(char(10),getdate(),102),'.','-')as date), dbo.DimDate.Date) as ndaysago from dbo.DimDate

select top 10 Datediff(MONTH, CAST(replace(convert(char(10),getdate(),102),'.','-')as date), dbo.DimDate.Date) as nmonthsago from dbo.DimDate

select top 10 Datediff(YEAR, CAST(replace(convert(char(10),getdate(),102),'.','-')as date), dbo.DimDate.Date) as nyearssago from dbo.DimDate

 

 

 

Thanks

Varun Devaraj

 

 


Thursday, April 28, 2016 - 2:34:19 PM - Arif Back To Top

I use the following code to get the Next_date, how will I make the SQL query which based on employee_id. I mean each employee next date and if there is one row for employee then Null

 

select 

  Id_employee,

  attend_date, 

  ID_Points,

  lead(attend_date) over (order by id_employee, attend_date) as next_date

from Attendance

 

 Id_employee attend_date     ID_Points next_date

1        2016-01-28 00:00:00            2 2015-07-31 00:00:00

9       2015-07-31 00:00:00            3 2015-12-02 00:00:00

9       2015-12-02 00:00:00            1 2016-01-12 00:00:00

9       2016-01-12 00:00:00            2 2016-04-04 00:00:00

9       2016-04-04 00:00:00            2 2015-12-02 00:00:00

16       2015-12-02 00:00:00            1 2015-12-14 00:00:00

39       2015-12-14 00:00:00            3 2015-07-19 00:00:00

40       2015-07-19 00:00:00            1 2015-08-15 00:00:00

40       2015-08-15 00:00:00            3 2015-08-19 00:00:00

40       2015-08-19 00:00:00            2 2015-09-13 00:00:00

 Thanks in Advance

 


Thursday, May 28, 2015 - 9:47:27 AM - J Back To Top

Refer to the string splitter function by Erikur Eriksson at http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/

Saturday, May 05, 2012 - 9:24:32 PM - Tony C Read The Tip

Interesting. I'm trying to think of a practical use for this functionality. Thoughts?

 


Friday, March 21, 2014 - 6:01:57 AM - Muthurajan Back To Top

Hi,

     I want to update current record value with previous record values. can i use lag function in update statement or is there any other techniques to do it in sql server 2012?

 

Thanks

Muthu


Friday, May 11, 2012 - 9:38:24 AM - Andrew Davis Back To Top

Sorry, typo in the SQL:

SELECT
z.msgcode
,z.Station
,z.value
,z.effectivefrom effectivefrom
,CASE
WHEN ( nextStation=Station AND nextmsgcode=msgcode)
THEN nextroweffectivedate
ELSE sysdate+9999 END effectiveto
FROM
(select
x.*
,lead(x.effectivefrom,1) over (order by msgcode,Station,effectivefrom) as nextroweffectivedate
,lead(x.station,1) over (order by msgcode,Station,effectivefrom) as nextStation
,lead(x.msgcode,1) over (order by msgcode,station,effectivefrom) as nextmsgcode
from
stationvalue x
) z


Friday, May 11, 2012 - 9:36:48 AM - Andrew Davis Back To Top

To Tony C - I have used something similar to create an effective date range.

I have values that are changing on an occasional basis (readings from a power station). By using the Lead function in Oracle, I have been able to generate a date range (i.e. a From_Datetime and To_Datetime columns). This proved very useful for retrieving the value for a particular point in time. The alternative (using a self join and MIN statement) was very slow on a 250m row table!

The Oracle SQL is here (should be self explanatory):

SELECT
z.msgcode
,z.Station
,z.value
,z.effectivefrom effectivefrom
,CASE
WHEN ( nextStation=Station AND nextmsgcode=msgcode)
THEN nextroweffectivedate
ELSE sysdate+9999 END effectiveto
FROM
(select
x.*
,lead(x.effectivefrom,1) over (order by msgcode,Station,effectivefrom) as nextroweffectivedate
,lead(x.BMUNITCODE,1) over (order by msgcode,Station,effectivefrom) as nextStation
,lead(x.msgcode,1) over (order by msgcode,bmunitcode,effectivefrom) as nextmsgcode
from 
 stationvalue x
 )  z


Saturday, May 05, 2012 - 9:50:04 PM - Tony C Back To Top

Seems to be borrowed to some extent from Oracle's analytical functions...LEAD, LAG (, , ) OVER ()


Saturday, May 05, 2012 - 9:24:32 PM - Tony C Back To Top

Interesting. I'm trying to think of a practical use for this functionality. Thoughts?


Tuesday, April 17, 2012 - 2:09:51 AM - Lakki Back To Top

 

Thanks for your post and Good to know new Functions


Monday, April 16, 2012 - 9:01:20 AM - jeevan Back To Top

handy functions....but why did it take so long........?

 


Monday, April 16, 2012 - 8:40:09 AM - Hassan Parthasarathy Back To Top

Good to know;

Thanks for the post!

Partha


Learn more about SQL Server tools