LAG in TSQL

LAG has introduced in SQL Server 2012 to access data from a Previous 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)

--Previous row
 SELECT empid, Year, sales,

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

Results:
empid Year sales PrevProfit

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

1 2000 100.00 0.00

1 2001 200.00 100.00

1 2002 300.00 200.00

2 2001 200.00 0.00

2 2002 300.00 200.00

 
Two rows earlier


SELECT empid, Year, sales,

LAG(sales, 2, 0) OVER (PARTITION BY empid ORDER BY Year) AS PrevProfit FROM Sales
 Results :

empid Year sales PrevProfit

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

1 2000 100.00 0.00

1 2001 200.00 0.00

1 2002 300.00 100.00

2 2001 200.00 0.00

2 2002 300.00 0.00

--YOY Profit

SELECT empid, Year, sales,

LAG(sales, 1, 0) OVER (PARTITION BY empid ORDER BY Year) AS PrevProfit,

sales-LAG(sales, 1, 0) OVER (PARTITION BY empid ORDER BY Year) as YOY FROM Sales
 
Results :


empid Year sales PrevProfit YOY

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

1 2000 100.00 0.00 100.00

1 2001 200.00 100.00 100.00

1 2002 300.00 200.00 100.00

2 2001 200.00 0.00 200.00

2 2002 300.00 200.00 100.00