The previous section was about running aggregates, which almost work in a cumulative approach. However, we also need to compare values, laying each value next to each other in order to monitor the progress of the value or its trend. For this kind of data exploration, SQL Server has the LAG and LEAD functions. Both functions use the OVER clause to sort data appropriately and to determine frames. The LAG function then searches backward for preceding values from the same column and sets it as its result, while the LEAD function searches for the next value in a dataset. Let's introduce a short example:
Our Sales.SalesOrderHeader table contains order dates (reduced using CTE to order years only to shorten the result ...
