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 
