LEAD in TSQL

LEAD has introduced in SQL Server 2012 to access data from a subsequent row in the same resultset without the use of self-join . LAG is a analytical function in a select statement to compare values in the current row with values in a previous row.



Create Table Sales( empid int, Year int, sales money)

insert into Sales values(1,2000,100)

insert into Sales values(1,2001,200)

insert into Sales values(1,2002,300)

insert into Sales values(2,2001,200)

insert into Sales values(2,2002,300)

--Subsequent Row   SELECT empid, Year, sales,

lead(sales, 1, 0) OVER (PARTITION BY empid ORDER BY Year) AS PrevProfit FROM Sales

Results :

empid Year sales PrevProfit

----------- ----------- --------------------- ---------------------

1 2000 100.00 200.00

1 2001 200.00 300.00

1 2002 300.00 0.00

2 2001 200.00 300.00

2 2002 300.00 0.00