Returning Previous or Next values as a column in SQL

Returning Previous or Next values as a column in SQL

Sometimes the requirement is like we want to show next or previous values of the column.
For Eg In a employee table which have Employee details including salary and we want to know what is the employee current salary what was employee's salary before Hike so this we can achieve through Corelated sub queries.

Below I will demonstrate this using Employee table which contain's employee salary column storing employee current salary as well as previous salary in a single column but while fetching records we have to show previous salary as one column and current salary as new column.


--Employee Table Script 
CREATE TABLE [dbo].[Employee1](
[Empid] [int] NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'sumit', 6000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'amit', 9000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (6, N'vijay', 5000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (8, N'suresh', 5800)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 1300)
GO


If you will see above table some Employee like Sumit ,Amit ,vijay and Suresh has two salaries the lesser salary is salary before hike and the greater salary is salary after hike So we have to fetch this in sql query.

So below is the solution using Co-Related sub query.

--Query showing currentsalary and salary before hike
select Empid ,Empname, Empsalary as EmpCurrentSalary,(select Max(empsalary) from Employee1 E1 where e1.EmpSalary <e.empsalary
and e1.EmpName =e.EmpName ) as SalaryBeforeIncrement from Employee1 E

Output

Empid Empname EmpCurrentSalary SalaryBeforeIncrement
1              sumit           5000 NULL
2 sumit        6000                    5000
3 amit 8000 NULL
4 amit             9000 8000
5             vijay 10000 5000
6             vijay              5000                                  NULL
7 suresh 5000 NULL
8 suresh 5000 NULL
9 Mukesh        1300 NULL