Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
As a consultant and training provider in business intelligence projects on SQL Server, some analytic functions I expected to be announced for a long time were announced with the SQL Server 2012 version. We will examine two of these 8 new analytic functions, which are announced with SQL Server 2012: LEAD and LAG.
LEAD Function in SQL Server(TSQL):
The lead function allows you to move forward from the current row within the targeted result set. This action may be on the entire result set, or it may be on a specified part of the result set. In this way, we can compare the values in the table as we need without self-joining the table.
Now let’s look at the basic template:
1 2 | LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ([ partition_by_clause] order_by_clause) |
The lead function consists of two basic parts.
First Part:
There is scalar expression, offset and default value if there is no data. There are a few things to be aware of at this point. The function written in (scalar_expression) must definitely return a single(scalar) value. Another issue with this section is that no other analytic function should be used in scalar expression.
In this section, we will decide the column that will be sorted and whether or not to be grouped according to a column.
Let’s make examples for a better understanding of LEAD Function.
In our examples, we used the AdventureWorks database that was create for SQL Server 2008R2.
Example 1:
1 2 3 4 5 6 | USE AdventureWorks2008R2 GO SELECT BusinessEntityID,YEAR(QuotaDate) AS SalesYear,Month(QuotaDate)/3+1 AS SalesQuarter, SalesQuota AS CurrentQuota, LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota FROM Sales.SalesPersonQuotaHistory WHERE BusinessEntityID = 279 AND YEAR(QuotaDate) = 2006; |
When I run the above query, the sales quota data returned on a quarterly basis, as you saw in the result. With the LEAD function, we have added the next period sales quota data to each line. One important point here is that the NextQuota value returns 0 for the last quarter of 2006 because this data is a data of 2007. Although the data for the last quarter existed in the table, it was not listed in the result set.
In our example, if you make a change in the Lead function as follows, you can see that the process is successful. This also explains why it is called as scalar_expression.
1 | LEAD(SalesQuota+10, 1,0) OVER (ORDER BY YEAR(QuotaDate)) |
Second Part:
OVER Clause in the lead function:
When we examine the query and the results, we see that the Order BY process in OVER Clause has been proceed before the Order By Clause at the end of the query and the LEAD function is processed according to this order. Then we see that Order By Clause at the end of the query is proceed.
1 2 3 4 5 | SELECT BusinessEntityID,YEAR(QuotaDate) AS SalesYear,Month(QuotaDate)/3+1 AS SalesQuarter, SalesQuota AS CurrentQuota, LEAD(SalesQuota+5, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota FROM Sales.SalesPersonQuotaHistory WHERE BusinessEntityID = 279 AND YEAR(QuotaDate) = 2006 ORDER BY SalesQuarter DESC |
Partition By Clause:
When we examine the query and its result, we see that it is grouped according to the BusinessEntityID in Partition By Clause.
1 2 3 4 5 | SELECT BusinessEntityID,YEAR(QuotaDate) AS SalesYear, Month(QuotaDate)/3+1 AS SalesQuarter,SalesQuota AS CurrentQuota, LEAD(SalesQuota+5, 1,0) OVER (PARTITION BY BusinessEntityID ORDER BY YEAR(QuotaDate)) AS NextQuota FROM Sales.SalesPersonQuotaHistory WHERE YEAR(QuotaDate) = 2006 AND BusinessEntityID in (274,275) |
LAG Function in SQL Server(TSQL):
In contrast to the LEAD function, the LAG function allows a movement in the reverse direction.
LAG function allows you to move backward from the current row within the targeted result set. This action may be on the entire result set, or it may be on a specified part of the result set. In this way, we can compare the values in the table as we need without self-joining the table.
Now let’s look at the basic template:
1 2 | LAG ( scalar_expression [ ,offset ] , [ default ] ) OVER ([ partition_by_clause] order_by_clause) |
The rules in the LEAD function are valid here. In the examples in the lead function, it is enough to write PreviousQuota instead of NextQuota and LAG instead of LEAD.
1 2 3 4 5 | SELECT BusinessEntityID,YEAR(QuotaDate) AS SalesYear, Month(QuotaDate)/3+1 AS SalesQuarter,SalesQuota AS CurrentQuota, LAG(SalesQuota+5, 1,0) OVER (PARTITION BY BusinessEntityID ORDER BY YEAR(QuotaDate)) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory WHERE YEAR(QuotaDate) = 2006 AND BusinessEntityID in (274,275) |
