Lag Examples

Here is an example of using the LAG function in SQL 2012.

A more detailed example follows with the UPDATE that follows.

# Apply, Unbounded, Lag, Lead Samples

Select c.customerid, c.accountnumber, o.*
from sales.Customer as c
OUTER APPLY (
	select top(5) soh.OrderDate, soh.SalesOrderID From Sales.SalesOrderHeader as soh
	where soh.CustomerID = c.CustomerID
	order by soh.OrderDate DESC
	) as o
Where c.TerritoryID = 3
 
 
 
/* Running total example */
select * 
	.LAG(Amount, 1, 0) OVER (PARTITION BY AccountID)ORDER BY  AccountID, TransactionDate, TransactionID)
	, SUM(Amount) OVER (PARTITION BY AccountID) AS FinalBalance
	, SUM(Amount) OVER (
		PARTITION BY AccountID
		ORDER BY TransactionDate, TransactionID
		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- or CURRENT ROW and UNBOUNDED FOLLOWING
	) AS CurrentBalance
from transactions
WHERE AccountID = 29825
ORDER BY AccountID, TransactionDate, TransactionID

Here is how to create a LAG function in pre-2012 SQL, using a self join.

Borrowed from: http://stackoverflow.com/questions/22188514/alternate-of-lead-lag-function-in-sql-server-2008

select t.*
from table t join
     table tnext
     on t.id = tnext.id - 1 and
        t.StatusId = 1 and
        tnext.StatusId = 6 and
        datediff(second, t.MinStartTime, tnext.MinStartTime) < 60;

UPDATED: Another example

In this example I want to pull a query that will give me bytes per second.

I have a table where performance metrics are written each minute of the hour. The Metric records the accumulated total bytes. That means I will need to compute the difference between the totals to see how many bytes were written during said interval.

First I start with this query that pulls the data I will need. The table has one entry per minute. But since I only want a data point of once every ten minutes, I’ll pull the maximum value from each ten minute period.

SELECT  DateKey, 
	CONCAT(LEFT(TimeKey,3),'000') AS TimeKey,
	MAX(iVal) AS ReadBytesCounter 	
FROM	Analysis.PerfCounterStats

Since the counter is a running total, I will need to find the value of the PREVIOUS row in order to subtract it from the current row. I can do this by using the LAG function.

SELECT  DateKey, 
	CONCAT(LEFT(TimeKey,3),'000') AS TimeKey,
	MAX(iVal) AS ReadBytesCounter, 	
	LAG(MAX(iVal),1) OVER ( ORDER BY LocationID, ClusterID, DateKey, LEFT(TimeKey,3)) As MyLag
FROM	Analysis.PerfCounterStats
WHERE	(MetricID = 363) and locationid=101 and clusterid =701 and datekey = CONVERT(VARCHAR(10), GETDATE(),112)
GROUP BY LocationID, ClusterID, DateKey, LEFT(TimeKey,3);

Now I have a table with the current value and the value from the pervious row. I will now evaluate the difference and compute my total. I will add the following to my select statement that subtracts the last column from the previous to give me the difference. I’ll also divide it by 600 since there are 600 seconds in the ten minute time period I’m looking at.

(MAX(iVal) - (LAG(MAX(iVal),1) OVER ( ORDER BY LocationID, ClusterID, DateKey, LEFT(TimeKey,3))) ) / 600 AS BytesSec   -- 10 minutes = 600 sec