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 :


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