Category: Data Design

SQL Server 2012 has two new analytical functions, LEAD() AND LAG(). These functions return data from Next row (LEAD) and Previous row (LAG) of the same dataset without using self-join.

Let’s go with an example;

This is my initial dataset;

Here is the query to create this dataset;

IF OBJECT_ID('tempdb..#SampleDataSet') is not null drop table #SampleDataSet

SELECT *
INTO #SampleDataSet
FROM
(
	SELECT 1 LineId, '2017-04-01' StockId, 'Toyota' AS Make, 'Brown' AS Color, 'Sale' AS ActionType, '2017-4-15' ActionDate UNION
	SELECT 2 LineId, '2017-04-01' StockId, 'Toyota' AS Make, 'Brown' AS Color, 'Quote' AS ActionType, '2017-4-10' ActionDate UNION
	SELECT 3 LineId, '2017-05-01' StockId,'Toyota' AS Make, 'Red' AS Color, 'Sale' AS ActionType, '2017-5-15' ActionDate UNION
	SELECT 4 LineId, '2017-05-01' StockId, 'Toyota' AS Make, 'Red' AS Color, NULL AS ActionType, '2017-5-10' ActionDate UNION
	SELECT 5 LineId, '2017-06-01' StockId, 'Toyota' AS Make, 'Pink' AS Color, 'Sale' AS ActionType, '2017-6-15' ActionDate UNION
	SELECT 6 LineId, '2017-06-01' StockId, 'Toyota' AS Make, 'Pink' AS Color, 'Quote' AS ActionType, '2017-6-10' ActionDate UNION
	SELECT 7 LineId, '2017-07-01' StockId, 'Toyota' AS Make, 'Green' AS Color, 'Sale' AS ActionType, '2017-7-15' ActionDate UNION
	SELECT 8 LineId, '2017-07-01' StockId, 'Toyota' AS Make, 'Green' AS Color, 'Quote' AS ActionType, '2017-7-10' ActionDate UNION
	SELECT 9 LineId, '2017-08-01' StockId, 'Honda' AS Make, 'Silver' AS Color, 'Sale' AS ActionType, '2017-8-15' ActionDate UNION
	SELECT 10 LineId, '2017-08-01' StockId, 'Honda' AS Make, 'Silver' AS Color, 'Quote' AS ActionType, '2017-8-10' ActionDate UNION
	SELECT 11 LineId, '2017-09-01' StockId, 'Honda' AS Make, 'Brown' AS Color, 'Sale' AS ActionType, '2017-9-15' ActionDate UNION
	SELECT 12 LineId, '2017-09-01' StockId, 'Honda' AS Make, 'Brown' AS Color, NULL AS ActionType, '2017-9-10' ActionDate UNION
	SELECT 13 LineId, '2017-10-01' StockId, 'Honda' AS Make, 'Red' AS Color, 'Sale' AS ActionType, '2017-10-15' ActionDate UNION
	SELECT 14 LineId, '2017-10-01' StockId, 'Honda' AS Make, 'Red' AS Color, 'Quote' AS ActionType, '2017-10-10' ActionDate
) src
WHERE 1=1

/*
	This is my initial dataset
*/

SELECT * FROM #SampleDataSet src

I am adding SeqCount and Seq column to identify each group of car (make and color) and each action in the group. LAG and Lead values of LineId column are used to demonstrate function output.

SELECT 
	src.LineId, src.StockId, 
	ROW_NUMBER() OVER (PARTITION BY StockId ORDER BY ActionDate DESC) Seq, 
	COUNT(*) OVER (PARTITION BY StockId) SeqCount, 
	src.Make, src.Color, src.ActionType, src.ActionDate,
	LEAD(src.LineId) OVER (ORDER BY src.LineId) LeadValue,
	LAG(src.LineId) OVER (ORDER BY src.LineId) LagValue
FROM #SampleDataSet src

This is the output of above query;

You can see the application of LEAD() and LAG() function in last two columns on the right side of above dataset.

One of the possible application of this function. As a business rule every quote action must be preceded by sale action in a car dealer dataset. If quote action is null in a group, fix it.

SELECT 
	src.StockId, src.Seq, src.SeqCount, src.Make, src.Color, src.ActionType,
	CASE WHEN src.LeadValue = 'Sale' AND src.ActionType IS NULL THEN 'Quote' ELSE src.ActionType END dActionType,
	src.ActionDate, src.LeadValue, src.LagValue
FROM
(
	--Dataset transformation
	SELECT 
		src.StockId, 
		ROW_NUMBER() OVER (PARTITION BY StockId ORDER BY ActionDate DESC) Seq, 
		COUNT(*) OVER (PARTITION BY StockId) SeqCount, 
		src.Make, src.Color, src.ActionType, src.ActionDate,
		LEAD(ActionType) OVER (ORDER BY ActionDate DESC) LeadValue,
		LAG(ActionType) OVER (ORDER BY ActionDate DESC) LagValue
	FROM #SampleDataSet src
) src
WHERE 1=1

Here is the output;

dActionType is a derived column that has all the correct sequence of actions with the help of Lead() and LAG() function.

Resources

https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017