Technical Article

Getting LEAD and LAG values manually

,

Getting Previous is next values is always a need for any database professional, specially Analyst and Developers, SQL Server provide built in Functions for this purpose which are introduced in SQL Server 2012, This Script is useful to get same results in Earlier versions like 2008, 2008R2 etc.

We can get  our result in three easy steps

1.        create a Table and insert some dummy records for Demo.

2.       Inserting Dummy Records

3. Query to see LEAD and LAG Values

Here is your Previous Values /LAG Function Result

and here is your Next Values /LEAD Function Result

See you next time guys .....

/****Create Demo Table ****/CREATE TABLE Employee
(
Empid int NOT NULL,
Name nchar(10) NULL,
City nchar(10) NULL
) ON [PRIMARY]
GO


/***Insert Values in Demo Table *****/Insert into Employee 
Values(1245,'George','Jax'), (1045,'Peter','Anadale'), (1157,'John','Dallas'),
      (1175,'Pete','Topeka'), (875,'Petron','Vienna'), (2311,'Kohli','Mumbai'),
      (1547,'Peter','Kansas'), (3514,'Abian','KHI'), (4251,'Ghani','Alexandria'),
      (957,'Ahmed','Vienna'), (1084,'Bhanu','Manderin'), (2954,'Ganeshan','Mcclean')
      



select * from Employee





/**** For LAG Value ****/WITH TEMP AS
(
select  
 ROW_NUMBER() OVER (order by empid) Record_No, 
 Empid, Name, City
FROM Employee
)
select  a.empid, a.name, a.city, b.name Previous_Employee
 from  TEMP a LEFT OUTER JOIN  temp b 
 ON a.Record_no = b.Record_no +1
 


 
 
 /**** For LEAD Value ****/WITH TEMP AS
(
select  
 ROW_NUMBER() OVER (order by empid) Record_No, 
 Empid, Name, City
FROM Employee
)
select  a.empid, a.name, a.city, b.name Next_Employee
 from  TEMP a LEFT OUTER JOIN  temp b 
 ON a.Record_no = b.Record_no -1

Rate

4 (10)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (10)

You rated this post out of 5. Change rating