Thursday, October 29, 2015

Lead and Lag functions in SQL Server 2012

CREATE TABLE [dbo].[Employees1]
(
[Id] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Dept] [nvarchar](50) NULL,
[Gender] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[Salary] [float] NULL,
)

Insert into Employees Values(1,'John','IT','Male','UK',5000)
Insert into Employees Values(2,'Mary','HR','Female','India',3000)
Insert into Employees Values(3,'Todd','IT','Male','UK',3500)
Insert into Employees Values(4,'Pam','HR','Female','India',4000)
Insert into Employees Values(5,'Tatcher','Payroll','Male','USA',2000)
Insert into Employees Values(6,'Sunil','IT','Male','USA',1400)
Insert into Employees Values(7,'Hari','Payroll','Male','UK',2500)
Insert into Employees Values(8,'Sunitha','HR','Female','India',4000)
Insert into Employees Values(9,'Sneha','IT','Female','India',3000)
Insert into Employees Values(10,'Ruby','Payroll','Male','UK',4600)

Select Name, Gender, Salary
LEAD(Salary) Over (Order by Salary) as LEADS,
LAG(Salary) Over (Order by Salary) as LAGS
From Employees

Select Name, Gender, Salary
LEAD(Salary,2,-1) Over (Order by Salary) as LEADS,
LAG(Salary,1,-1) Over (Order by Salary) as LAGS
From Employees


Select Name, Gender, Salary
LEAD(Salary,2,-1) Over (PARTITION BY Gender Order by Salary) as LEADS,
LAG(Salary,1,-1) Over ( PARTITION BY Gender Order by Salary) as LAGS
From Employees