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
(
[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