The LEAD and LAG functions in SQL Server 2012

The LEAD function provides value from the next row in result of query.
The LAG function provides value from the previous row in result of query.
You may compare values between current value and previous value or between current value and follow value return in the same query.
Begin with create new 2 tables: person and salary and inserting data in it.

 create table person (
id int primary key identity,
name varchar(120)
);

create table salary (
id int primary key identity,
year int,
month int,
value money,
id_person_fk int foreign key references person(id)
);

insert into person(name) values
('John'),('Mary'),
('Eva'),('Carl');

insert into salary(year, month, value,id_person_fk) values
(2009,10,2100,1),(2009,10,2760,2),(2009,10,5100,4),
(2009,11,4300,4),(2009,11,2260,1),(2009,11,2500,2),
(2009,12,1800,1),(2009,12,2260,2),(2009,12,6700,3),(2009,12,4100,4),
(2010,1,1950,1),(2010,1,2340,2),(2010,1,5800,3),(2010,1,5000,4),
(2010,2,2450,1),(2010,2,2140,2),(2010,2,4800,3),(2010,2,4500,4),
(2010,3,2550,1),(2010,3,2760,2),(2010,3,3900,3),(2010,3,4000,4),
(2011,1,1950,1),(2011,1,2340,2),(2011,1,5800,3),
(2011,2,3150,1),(2011,2,3240,2),(2011,2,5100,3),
(2011,3,3250,1),(2011,3,3160,2),(2011,3,5600,3),(2011,3,7000,4);

As first example select name of every person, year, month and  salary.

SELECT p.name, s.year, s.month, s.value  
FROM person p
JOIN salary s on s.id_person_fk = p.id ;

You notice in one year one person gave several salary.

w1

The LEAD function provides value from the next row. The basic syntax is follow:

LEAD(expression) OVER(ORDER BY columns)

and You must consider these all  elements in syntax.
The LAG function looks like LEAD, diffrent is only name of function: LAG.
In the first two examples below is use these functions.

SELECT LEAD(s.value) OVER (ORDER BY s.value)  as next, 
            p.name, s.year, s.value  
FROM person p
JOIN salary s on s.id_person_fk = p.id ;

w2

SELECT LAG(s.value) OVER (ORDER BY s.value)  as prev, 
       p.name, s.year, s.value  
FROM person p
JOIN salary s on s.id_person_fk = p.id ;

w3

If You notice when the value is not exists in empty place is set null. In LEAD or LAG as second and third parameter it is possible determine:  the number of rows forward from the current row from which to obtain a value and default value which replace null. The default value for second parameter is 1 and for third is null.
Let see another examples with it.

SELECT LEAD(s.value,1,0.00) OVER (ORDER BY s.value)  as next, 
            p.name, s.year, s.value  
FROM person p
JOIN salary s on s.id_person_fk = p.id ;

w4

SELECT LAG(s.value,1,0.00) OVER (ORDER BY s.value)  as prev, 
       p.name, s.year, s.value  
FROM person p
JOIN salary s on s.id_person_fk = p.id ;

w5

In OVER besides ORDER BY is used either PARTITION BY. After it should provide column according which will be grouping result set.

SELECT LEAD(s.value) 
OVER (PARTITION BY p.name ORDER BY s.value)as next,
      p.name, s.year, s.value  
FROM person p
JOIN salary s on s.id_person_fk = p.id ;

w6

SELECT LAG(s.value) 
OVER (PARTITION BY p.name ORDER BY s.value) as prev, 
     p.name, s.year, s.value  
FROM person p
JOIN salary s on s.id_person_fk = p.id ;

w7

The examples above with PARTITION using may containt either 3 parametrs in LEAD or LAG(default value in third parameter).

SELECT LEAD(s.value,1,0.00) 
OVER (PARTITION BY p.name ORDER BY s.value)as next,
      p.name, s.year, s.value  
FROM person p
JOIN salary s on s.id_person_fk = p.id ;

w8

SELECT LAG(s.value,1,0.00) 
OVER (PARTITION BY p.name ORDER BY s.value) as prev, 
     p.name, s.year, s.value  
FROM person p
JOIN salary s on s.id_person_fk = p.id ;

w9
In last example the previous salary is getting not form one row previous but two rows previous.

SELECT LAG(s.value,2,0.00) 
OVER (PARTITION BY p.name ORDER BY s.value) as prev, 
     p.name, s.year, s.value  
FROM person p
JOIN salary s on s.id_person_fk = p.id ;

w10

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.