Growth looks different when the comparison skips a month with no transactions. A year-over-year query must align calendar periods before calculating growth. The previous twelve rows are not automatically the previous year's same month.
Define the Year-Over-Year Monthly Measure
Decide what sales means before choosing a window function. Gross orders, invoiced value, paid revenue, and net returns produce different totals. Apply the same definition to both years and keep exclusions consistent.
The report grain is one row per calendar month for one series. Product, location, or currency adds separate series that need independent comparisons. Do not combine incompatible currencies merely because their values share a numeric data type.
I check month completeness before interpreting a growth percentage. I also ask whether the current month is final or still accumulating transactions. Comparing a partial month with a complete prior month changes the question substantially.
The sample uses a complete fictional ledger for 2024 and 2025, with sparse transactions. Missing transaction months therefore mean zero sales within that known coverage. Outside verified coverage, a missing month must remain unknown rather than being manufactured as zero.
Aggregate Transactions to Month Starts
Run the following setup in one connection. Sales amounts are declared inputs, not observed production figures. The explicit zero in February 2024 helps separate a zero denominator from a missing transaction month.
DROP TABLE IF EXISTS #YearSales;
CREATE TABLE #YearSales
(
SaleId int NOT NULL PRIMARY KEY,
SaleDate date NOT NULL,
Amount decimal(12,2) NOT NULL
);
INSERT #YearSales VALUES
(1,'20240110',100),(2,'20240210',0),(3,'20241210',90),
(4,'20250110',120),(5,'20250210',50),(6,'20250310',75);
SELECT DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1) AS MonthStart,
SUM(Amount) AS SalesTotal
FROM #YearSales
GROUP BY DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1)
ORDER BY MonthStart;The aggregation produces only months represented by source rows. That output is suitable for a date-based join, but not yet for a twelve-row offset. A year-over-year calculation using LAG on this sparse result would select the wrong logical period or no period.
Using month starts gives the series a stable date key. It also avoids separately handling December-to-January year changes in ordering. Keep the original range predicate on SaleDate when limiting large source tables so indexing can support source selection.
Supply a Calendar before Using LAG
The next query builds every month from January 2024 through December 2025. It left joins totals and fills known empty months with zero. The declared ledger coverage is the reason that replacement is valid here.
DROP TABLE IF EXISTS #MonthSeries;
;WITH Digits AS
(
SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)
), Calendar AS
(
SELECT DATEADD(month, a.n + 10*b.n, CONVERT(date,'20240101')) AS MonthStart
FROM Digits AS a CROSS JOIN Digits AS b
WHERE a.n + 10*b.n < 24
), Monthly AS
(
SELECT DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1) AS MonthStart,
SUM(Amount) AS SalesTotal
FROM #YearSales
WHERE SaleDate >= '20240101' AND SaleDate < '20260101'
GROUP BY DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1)
)
SELECT c.MonthStart,
COALESCE(m.SalesTotal, CONVERT(decimal(38,2),0)) AS SalesTotal
INTO #MonthSeries
FROM Calendar AS c
LEFT JOIN Monthly AS m ON m.MonthStart = c.MonthStart;
CREATE UNIQUE CLUSTERED INDEX IX_MonthSeries
ON #MonthSeries(MonthStart);
;WITH Compared AS
(
SELECT MonthStart, SalesTotal,
LAG(SalesTotal,12) OVER (ORDER BY MonthStart) AS PreviousYearTotal
FROM #MonthSeries
)
SELECT MonthStart, SalesTotal, PreviousYearTotal,
SalesTotal - PreviousYearTotal AS ValueChange,
(SalesTotal - PreviousYearTotal) / NULLIF(PreviousYearTotal,0)
* CONVERT(decimal(5,2),100) AS GrowthPercent
FROM Compared
WHERE MonthStart >= '20250101'
ORDER BY MonthStart;LAG now moves back twelve monthly rows because every month exists exactly once. The outer filter runs after the window calculation. Filtering away 2024 before LAG would remove the very rows needed for the comparison.
For January 2025, the declared inputs imply growth from 100 to 120. February and March have zero prior-year sales under the coverage assumption. Their percentage change is undefined, even though their absolute increases remain meaningful.
When multiple series share the table, partition LAG by the complete series key. Build the calendar for every valid series, not merely dates globally. Otherwise, absent product-month rows can still break the twelve-row interpretation.
Align Year-Over-Year Dates with a Self-Join
A date-aligned self-join makes the previous-year relationship explicit. It does not depend on a row offset. The following version uses the complete sample calendar so zero and absent prior coverage retain distinct meanings.
SELECT currentMonth.MonthStart,
currentMonth.SalesTotal,
previousMonth.SalesTotal AS PreviousYearTotal,
currentMonth.SalesTotal - previousMonth.SalesTotal AS ValueChange,
(currentMonth.SalesTotal - previousMonth.SalesTotal)
/ NULLIF(previousMonth.SalesTotal,0)
* CONVERT(decimal(5,2),100) AS GrowthPercent,
CASE
WHEN previousMonth.MonthStart IS NULL THEN N'Prior coverage unavailable'
WHEN previousMonth.SalesTotal = 0 THEN N'Prior total is zero'
ELSE N'Comparable'
END AS ComparisonStatus
FROM #MonthSeries AS currentMonth
LEFT JOIN #MonthSeries AS previousMonth
ON previousMonth.MonthStart = DATEADD(year,-1,currentMonth.MonthStart)
WHERE currentMonth.MonthStart >= '20250101'
ORDER BY currentMonth.MonthStart;A self-join can also use sparse monthly totals, but missing rows then require a coverage decision. No source row does not distinguish no sales from an incomplete import. A calendar joined to known completeness metadata can make that distinction explicit.
Ensure one row per month and series before joining. Duplicate monthly summaries multiply the result and corrupt both comparisons and totals. An enforced unique key is helpful evidence that the intended reporting grain remains intact.
Keep Zero, Missing, and Negative Baselines Honest
NULLIF prevents division by a zero prior-year total. Returning NULL is more truthful than assigning infinite growth or silently labeling the increase 100 percent. Show the absolute change alongside a status explaining why the percentage is unavailable.
A missing prior year is also different from zero. The first year in the calendar has no comparable prior coverage in this dataset. Keep that uncertainty visible rather than replacing every missing comparison with zero.
Negative prior totals require a stated business interpretation. Net returns or corrections can produce a negative denominator and counterintuitive growth signs. Do not apply the standard positive-revenue percentage without explaining what the result means.
Decimal arithmetic prevents integer truncation, but precision and scale still deserve review. Round for display after calculating the ratio at sufficient precision. Test extreme totals and tiny nonzero baselines because they can create very large percentage values.
Reconcile Both Methods and the Reporting Boundary
Compare the LAG and date-join results for the same complete series. Their matching definitions should produce equivalent prior-month values. A disagreement points to calendar gaps, duplicate grains, inconsistent partitions, or filters applied at different stages.
I include a missing transaction month and a zero baseline in every year-over-year test. I also include the first available year and an incomplete current month. These cases expose assumptions that a smooth sales sample leaves hidden.
Which months are fully collected, and which are still open? Put that information beside the report's totals. A calendar is excellent at finding missing months, but it cannot interview the import job about why they are missing.
Use the same cutoff policy for both years when reporting partial periods. That can require day-aligned comparisons instead of completed monthly totals. Once the metric and coverage are fixed, the query can express them in one reproducible calculation.
Fiscal calendars introduce another alignment rule. A business using four-week periods needs the fiscal period mapping rather than a calendar-month offset. Preserve that mapping as data and join the corresponding prior-year fiscal period explicitly.
Late corrections also change the comparison population. Decide whether reports restate prior months or freeze previously published totals. Keep the source cutoff with each run so a revised year-over-year number can be explained from actual included transactions.
Related reading on this blog: A Walkthrough: DATETRUNC Function in SQL Server and SQL SERVER Challenge: Cumulative Total Calculation.
A growth percentage is not a standalone fact, it is a comparison between aligned and understood periods.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
