LAG and LEAD for SQL Server 2008

The LAG and LEAD analytical functions in SQL Server 2012 are very useful. Unfortunately, these functions don’t exist prior to the 2012 version. Most pre-2012 solutions resort to using self joins to identify the lagging or leading row. Here’s a solution that references the source table once:


WITH balance_details
AS
(
SELECT * FROM (
VALUES
('Tom','20140101',100),
('Tom','20140102',120),
('Tom','20140103',150),
('Tom','20140104',140),
('Tom','20140105',160),
('Tom','20140106',180),
('Jerry','20140101',210),
('Jerry','20140102',240),
('Jerry','20140103',230),
('Jerry','20140104',270),
('Jerry','20140105',190),
('Jerry','20140106',200),
('David','20140101',170),
('David','20140102',230),
('David','20140103',240),
('David','20140104',210),
('David','20140105',160),
('David','20140106',200)
) AS t (customer, balancedate,balance)
),
balance_cte AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY customer ORDER BY balancedate) rn
,(ROW_NUMBER() OVER (PARTITION BY customer ORDER BY balancedate))/2 rndiv2
,(ROW_NUMBER() OVER (PARTITION BY customer ORDER BY balancedate) + 1)/2 rnplus1div2
/*
,COUNT(*) OVER (PARTITION BY customer) partitioncount
*/
,customer
,balancedate
,balance
FROM balance_details
)
SELECT
rn
,rndiv2
,rnplus1div2
,customer,balancedate,balance
,CASE WHEN rn%2=1 THEN MAX(CASE WHEN rn%2=0 THEN balance END) OVER (PARTITION BY customer,rndiv2) ELSE MAX(CASE WHEN rn%2=1 THEN balance END) OVER (PARTITION BY customer,rnplus1div2)
END AS balance_lag
,CASE WHEN rn%2=1 THEN MAX(CASE WHEN rn%2=0 THEN balance END) OVER (PARTITION BY customer,rnplus1div2) ELSE MAX(CASE WHEN rn%2=1 THEN balance END) OVER (PARTITION BY customer,rndiv2)
END AS balance_lead
/*
,MAX(CASE WHEN rn=1 THEN balance END) OVER (PARTITION BY customer) AS first_value
,MAX(CASE WHEN rn=partitioncount THEN balance END) OVER (PARTITION BY customer) AS last_value
,MAX(CASE WHEN rn=4 THEN balance END) OVER (PARTITION BY customer) AS fourth_value
*/
FROM balance_cte
ORDER BY customer,balancedate

The results are as follows:

laglead

(The commented out code shows how to obtain the equivalent of the SQL 2012 FIRST_VALUE and LAST_VALUE. I also show how to obtain the Nth value where I’ve specified N as 4)

Regarding the LAG code it relies on the fact that integer division by 2 of the ROW_NUMBER can be used in the partitioning function. For example, 2 and 3 div 2 give 1. When the the current row is 3 (ie rn mod 2 is 1) we examine the window frame rows where rndiv2 is 1 and choose the balance value where the frame row is even (ie rn mod 2 is 0). That is, the value for row 2. When the current row is 2 (ie rn mod 2 is 0) we examine the window frame rows where rnplus1div2 is 1 and choose the balance value where the frame row is odd (ie rn mod 2 is 1). That is, the value for row 1.

For LEAD the priniciple is the same, we just use the opposite ‘division by 2’ expression in the partition clause.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: