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 :
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgT9mZO_4YXdbqcdIuatH_o1g1R8GsNApT5kYmhFDbtvl0bhgoWptKMA_rxqwtYgWVnj42qWA7UUAK6fdf8NYzaFZOUTsJTMGoC9k4Pnn_GzQKSvtcCBqlGhAAmD3kMtQfI7M5D6PtJUvQn/s640/a1.JPG)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIn1bbJjJVbRn9gVAPkDjDBPQSeGFPW2nOlwkxGiIeYdy7MWiofZzOmHqWkKHWUWKd7CqinAYQYoeLmHZLChbdlGnwrbOyV5KUEVJbhaJLeVj0Fd6CvwW6-sMCuY_1uFQ-DS6I4l_3tyb4/s640/a2.JPG)
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
No comments:
Post a Comment