Each month's sales row already contains enough context to compare it with its neighbor. LAG and LEAD bring adjacent values into the same result. The ordering and partition rules decide what neighboring really means.
Give LAG and LEAD a Stable Order
LAG looks backward and LEAD looks forward under the window's ORDER BY. PARTITION BY restarts that sequence for each product. A deterministic ordering key is essential.
If two rows share the same sort key, add a stable tie-breaker or aggregate them to the intended grain first. The function follows the declared order. It doesn't infer which row was entered first or belongs to the prior business period.
I establish one row per product and month before calculating change. The sample table uses that pair as its primary key. Amounts and dates are invented inputs.
Run the setup in one query window. The next examples compare observed rows in the ordered set. Missing months need a calendar expansion if the business means previous calendar month instead of previous available record.
CREATE TABLE #MonthlySales
(ProductId int,MonthStart date,Amount decimal(19,4),PRIMARY KEY(ProductId,MonthStart));
INSERT #MonthlySales VALUES
(1,'20240101',100),(1,'20240201',120),(1,'20240301',90),
(2,'20240101',0),(2,'20240201',50);
SELECT ProductId,MonthStart,Amount,
LAG(Amount) OVER(PARTITION BY ProductId ORDER BY MonthStart) AS PreviousAmount,
LEAD(Amount) OVER(PARTITION BY ProductId ORDER BY MonthStart) AS NextAmount
FROM #MonthlySales;Calculate Change After the Window Value
Put the previous value in a CTE or derived table before using it in arithmetic. That keeps the window specification readable and avoids repeating it in several expressions. Difference is current minus previous.
Percent change divides that difference by the previous amount. Use decimal arithmetic and guard the denominator. A previous zero doesn't have a finite percentage change under that ordinary formula.
The query below returns NULL for an unavailable or zero denominator. That is an explicit undefined-result policy, not a measured zero change. The consumer should distinguish it from a valid percentage. For product 2, February shows a change of 50 and a NULL percentage because January was zero.
Casting or using a decimal factor avoids integer division. I check the final expression's numeric type for large amounts. Intermediate precision and scale also belong to the arithmetic contract.
WITH Compared AS
(
SELECT ProductId,MonthStart,Amount,
LAG(Amount) OVER(PARTITION BY ProductId ORDER BY MonthStart) AS PreviousAmount
FROM #MonthlySales
)
SELECT ProductId,MonthStart,Amount,PreviousAmount,
Amount - PreviousAmount AS AmountChange,
100.0 * (Amount - PreviousAmount) / NULLIF(PreviousAmount,0) AS PercentChange
FROM Compared
ORDER BY ProductId,MonthStart;Set the LAG and LEAD Offset and Default
The offset specifies how many ordered rows away the function looks. An offset of two isn't necessarily two calendar months when the dataset has gaps. The default argument supplies a value when that offset falls outside the partition.
It doesn't automatically replace a NULL stored in an existing neighboring row. Keep those two missingness cases separate when selecting a business default.
A zero default can be suitable for a cumulative calculation with an explicit baseline. It can be misleading in a period comparison because it invents prior activity. Decide that rule rather than using zero to eliminate a blank cell.
The sample shows a two-row offset and typed default. Inspect first and last rows under that definition before using the result to calculate a trend.
SELECT ProductId,MonthStart,Amount,
LAG(Amount,2,CONVERT(decimal(19,4),0))
OVER(PARTITION BY ProductId ORDER BY MonthStart) AS TwoRowsEarlier,
LEAD(Amount,1,CONVERT(decimal(19,4),0))
OVER(PARTITION BY ProductId ORDER BY MonthStart) AS NextOrDefault
FROM #MonthlySales;
Give LAST_VALUE the Intended Frame
FIRST_VALUE and LAST_VALUE use a window frame, unlike the simple neighbor lookup. With ORDER BY and the default frame, LAST_VALUE normally sees only through the current row or its peers. That doesn't mean the last value of the entire partition.
Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for the complete partition. That exposes its final ordered value to every row.
The query returns first and final amounts beside each month. The explicit frame makes that intent visible. Duplicate ordering keys still deserve a tie rule.
A frame can't decide which peer should represent the final business event. Keep the ordering stable before interpreting the result. The function's name sounds reassuringly complete, but its definition includes the frame it is allowed to see.
SELECT ProductId,MonthStart,Amount,
FIRST_VALUE(Amount) OVER(PARTITION BY ProductId ORDER BY MonthStart
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FirstAmount,
LAST_VALUE(Amount) OVER(PARTITION BY ProductId ORDER BY MonthStart
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FinalAmount
FROM #MonthlySales;Detect Changes in an Event Stream
Status transitions use the same neighbor pattern. Partition by the tracked entity and order by an event timestamp plus stable event identity. Compare current status with the preceding status.
Keep the first event according to the reporting rule. If status can be NULL, use a null-safe comparison. Ordinary inequality can hide a transition by returning UNKNOWN.
The next example uses SQL Server 2022's IS DISTINCT FROM for that comparison. ROW_NUMBER identifies the first event independently of whether its previous status is NULL. This avoids confusing an absent predecessor with an actual predecessor whose status was missing.
A sample event sequence should include both cases. The LAG and LEAD pattern needs to explain transitions, not merely remove rows whose text happens to repeat.
WITH Events AS
(
SELECT EntityId,EventId,Status
FROM (VALUES (1,1,N'Open'),(1,2,N'Open'),(1,3,N'Closed')) AS x(EntityId,EventId,Status)
), Compared AS
(
SELECT *,LAG(Status) OVER(PARTITION BY EntityId ORDER BY EventId) AS PreviousStatus,
ROW_NUMBER() OVER(PARTITION BY EntityId ORDER BY EventId) AS EventSequence
FROM Events
)
SELECT EntityId,EventId,PreviousStatus,Status
FROM Compared
WHERE EventSequence = 1 OR Status IS DISTINCT FROM PreviousStatus;Filter After LAG and LEAD, Not Before
A filter applied before the window function changes the set of rows it can see. Filtering to March first means LAG cannot see February. Compute the window over the required history, then filter the resulting rows when the report needs only the current period.
The same rule matters for status streams. An excluded event can change what previous status means for every later included event.
Which history boundary does this comparison need? Include enough rows to support the requested offset and frame. Use indexes aligned with partition and ordering columns where the workload supports them.
Inspect sorts and reads in the actual plan. Window functions remove a self-join from the expression, but they still need an ordered view of the relevant data. That work deserves a proper access path.
Review the Edge Rows Before the Middle
Test first rows, final rows, missing periods, repeated sort keys, zero amounts, and NULL statuses. Those reveal definition mistakes faster than a pleasant middle month. Keep the default and frame choices with the report specification.
A neighboring row is a result of the declared dataset and order. It isn't a calendar fact unless you built the dataset to represent every calendar period.
Use LAG and LEAD to make comparisons readable, with explicit partition and ordering rules. Calculate changes at the appropriate numeric scale and give undefined percentages a clear state. For FIRST_VALUE and LAST_VALUE, write the frame that matches the question.
The code becomes shorter than a self-join. The business definition still needs the same care, especially where the first or last row has nobody beside it.
Related reading on this blog: How to Access the Previous Row and Next Row value in SELECT statement? and What is T-SQL Window Function Framing? Notes from the Field #102.
A row comparison is not a guess about adjacency, it is a defined partition, order, and boundary rule.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
