Two new analytic functions LEAD and LAG are introduced in SQL Server 2012. LEAD is used to find the next row value and LAG is used to find the previous row value.
USE [AdventureWorks]
GO
-- Create tbl_temp Table
CREATE TABLE [dbo].[tbl_temp](
[code] [int] NOT NULL,
[type] [nvarchar](50) NOT NULL,
[quantity] [int] NOT NULL
)
GO
-- Insert records in the table
INSERT [dbo].[tbl_temp] ([code], [type], [quantity]) VALUES (1, N'Radio', 17)
GO
INSERT [dbo].[tbl_temp] ([code], [type], [quantity]) VALUES (2, N'Mobile', 22)
GO
INSERT [dbo].[tbl_temp] ([code], [type], [quantity]) VALUES (3, N'TV', 31)
GO
INSERT [dbo].[tbl_temp] ([code], [type], [quantity]) VALUES (4, N'Watch', 42)
GO
INSERT [dbo].[tbl_temp] ([code], [type], [quantity]) VALUES (5, N'DVD', 55)
GO
INSERT [dbo].[tbl_temp] ([code], [type], [quantity]) VALUES (6, N'CD', 90)
GO
-- See records from the table
SELECT * FROM tbl_temp
-- LEAD and LAG value
SELECT *, LEAD(quantity) OVER(ORDER BY quantity) AS LeadValue,
LAG(quantity) OVER(ORDER BY quantity) AS LagValue
FROM tbl_temp
-- LEAD and LAG with offset 2
SELECT *, LEAD(quantity,2) OVER(ORDER BY quantity) AS LeadValue,
LAG(quantity,2) OVER(ORDER BY quantity) AS LagValue
FROM tbl_temp
