SQL Server – "Denali" – Analytic Functions – LAG() and LEAD()
LAG() function can be used to access data from a previous row in the result set without using a self-join. And it’s counterpart LEAD() can be used to access data from a subsequent row in the same result set. These functions are introduced in “Denali” as T-SQL functions, these has been available as MDX functions since SQL Server 2005.
LAG() and LEAD() functions are also available in Oracle since Oracle 8i.
Syntax for LAG()/LEAD():
LAG/LEAD (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )
offset provides the numbers of rows back or forward the current row. default provides a default value when the scalar_expression returned by offset is NULL.
Below example uses LEG() and LEAD() to identify previous or next date for a particular order.
CREATE TABLE #Orders
(
OrderDate DATE,
ProductID INT,
Quantity INT
)
INSERT INTO #Orders VALUES
('2011-07-28',11,12), ('2011-03-18',12,74), ('2011-04-12',13,95),
('2011-07-25',14,57), ('2011-05-30',11,28), ('2011-05-21',10,12),
('2011-04-12',11,38)
SELECT OrderDate, ProductID, Quantity
FROM #Orders
Result Set:
OrderDate ProductID Quantity
———- ———– ———–
2011-07-28 11 12
2011-03-18 12 74
2011-04-12 13 95
2011-07-25 14 57
2011-05-30 11 28
2011-05-21 10 12
2011-04-12 11 38
(7 row(s) affected)
Last order date using LAG():
SELECT OrderDate, ProductID, Quantity,
LAG(OrderDate, 1, OrderDate)
OVER (PARTITION BY ProductID ORDER BY OrderDate)
AS Last_OrderDate
FROM #Orders
Result Set:
OrderDate ProductID Quantity Last_OrderDate
———- ———– ———– ————–
2011-05-21 10 12 2011-05-21
2011-04-12 11 38 2011-04-12
2011-05-30 11 28 2011-04-12
2011-07-28 11 12 2011-05-30
2011-03-18 12 74 2011-03-18
2011-04-12 13 95 2011-04-12
2011-07-25 14 57 2011-07-25
(7 row(s) affected)
Next order date using LEAD():
SELECT OrderDate, ProductID, Quantity,
LEAD(OrderDate, 1, OrderDate)
OVER (PARTITION BY ProductID ORDER BY OrderDate)
AS Next_OrderDate
FROM #Orders
Result Set:
OrderDate ProductID Quantity Next_OrderDate
———- ———– ———– ————–
2011-05-21 10 12 2011-05-21
2011-04-12 11 38 2011-05-30
2011-05-30 11 28 2011-07-28
2011-07-28 11 12 2011-07-28
2011-03-18 12 74 2011-03-18
2011-04-12 13 95 2011-04-12
2011-07-25 14 57 2011-07-25
(7 row(s) affected)
Hope This Helps! Cheers!
Reference : Vishal (http://SqlAndMe.com)
Filed under: Analytic Functions, Database, SQL, SQL "Denali", Sql And Me, SQLServer, Tips & Tricks, TSQL Tagged: analytic function denali, Database, denali, denali function, lag lead, lag(), lead(), Query, scripts, SQL, SQL "Denali", SQL 2005, SQL 2008, SQL 2008 R2, Sql And Me, SQL Server, Sql&Me, SqlAndMe, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, Vishal, Vishal Gajjar
