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:

(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.