SQL Server access Previous / Next rows and first / last row from partitioned resultset

From SQL Server 2012, new built-in analytic functions are introduced, using that we can easily access subsequent/previous row in the same result set without the use of a self-join and first/last value in an ordered set of values.

In this post, let us see some examples for LEAD(), LAG(), FIRST_VALUE() and LAST_VALUE()


Let us consider below sample data :

DECLARE @Tmp TABLE (Id INT IDENTITY, Prod CHAR(2), Sales INT, StrDt DATE, EndDt DATE )
INSERT @Tmp SELECT 'A',100,'2015-01-15','2015-02-15'
INSERT @Tmp SELECT 'A',150,'2015-02-16','2015-03-15'

INSERT @Tmp SELECT 'B',200,'2015-01-20','2015-02-15'
INSERT @Tmp SELECT 'B',100,'2015-02-16','2015-03-15'
INSERT @Tmp SELECT 'B',250,'2015-03-16','2015-04-10'

INSERT @Tmp SELECT 'C',300,'2015-01-25','2015-02-15'
INSERT @Tmp SELECT 'C',350,'2015-02-16','2015-05-10'

SELECT * FROM @Tmp

SELECT Id,Prod,StrDt,EndDt,Sales,
 LAG (Sales, 1) OVER (PARTITION BY Prod ORDER BY Id ) AS PrevSales
,LEAD (Sales, 1) OVER (PARTITION BY Prod ORDER BY Id ) AS NextSales
,FIRST_VALUE(StrDt) OVER (PARTITION BY Prod ORDER BY Id ) AS FirstDate
,LAST_VALUE(EndDt)  OVER (PARTITION BY Prod ORDER BY Id ROWS BETWEEN CURRENT ROW  AND UNBOUNDED FOLLOWING) AS LastDate
FROM @Tmp
ORDER BY Id

See Also:

• My T-SQL Posts
• My posts on SQL Server 2012

Reference:

• LEAD()
• LAG()
• FIRST_VALUE()
• LAST_VALUE()