LEAD and LAG functions in SQL 2012

SQL 2012 introduced two new analytical functions LEAD and LAG that would help accessing a preceding and subsequent rows in a query.

Consider the subsequent example, we have to find the employees who were absent for two consecutive days

Here is how you could do it in prior to SQL 2012

WITH CTE_EMP AS
  (SELECT *,
          row_number()OVER(Partition BY employeeid ORDER BY signindate) RowNum
   FROM @employee),
     CTE_Final AS
  (SELECT c1.EmployeeID,
          c1.SigninDate AbsenceStartsOn,
          datediff(DAY,c2.SigninDate,c1.SigninDate)-1 AbsentDays
   FROM cte_emp C1
     JOIN cte_emp C2 
   ON c1.EmployeeID=c2.EmployeeID
      AND c1.RowNum=c2.RowNum+1
   )
SELECT *
FROM CTE_Final
WHERE AbsentDays>1;

Now lets try to do this same  in SQL 2012, We will use the LAG,

Here is what MSDN says about LAG, You can read more about LAG here

“Accesses data from a previous row in the same result set without the use of a self-join in SQL Server 2012.”

SELECT 
	EmployeeID,
	SigninDate AbsenceStartsOn, 
	LAG(SigninDate)OVER(PARTITION BY EmployeeId ORDER BY SigninDate) AbsentEnds 
FROM @employee

The result of this query would look like this. Using a LAG function we were able to read the value from the preceding row.

And putting this together, the final solution would be much easier to acheive

WITH CTE_FINAL AS
(SELECT EmployeeID,
          SigninDate AbsenceStartsOn,
          DateDiff(DAY,LAG(SigninDate) OVER(Partition BY EmployeeId ORDER BY SigninDate),
          SigninDate)-1 AbsentDays
   FROM @employee)
SELECT *
FROM CTE_FINAL
WHERE AbsentDays>1;

In a similar fashion LEAD would help us accessing the values from the subsequent rows, Here is what MSDN says about LEAD, You can read more about LEAD here

“Accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2012”

SELECT
	EmployeeID,
	SigninDate AbsenceStartsOn, 
	LAG(SigninDate)OVER(PARTITION BY EmployeeId ORDER BY SigninDate) AbsentEnds 
FROM @employee

The query with LEAD function is performing better too, Here is the execution plan for both of these queries

These new analytical functions could be much handy in writing complex queries with less effort.