February disappears from the chart because no row was recorded that month. Reports with zero-filled gaps start from a complete calendar scaffold, then attach the counts that exist.
Plan Zero-Filled Gaps Before Counting Events
GROUP BY returns groups found in the input. It doesn't invent a month with no matching rows. A complete report therefore needs a separate list of required months.
Build that list from the report's requested boundaries. Each row represents one month start. Keep the excluded end at the first day after the final included month.
I check the calendar scaffold before changing the aggregation. An accurate count query can still feed an incomplete chart. The missing part is the list of expected groups.
The examples use UTC calendar months and synthetic events. A local-month report must derive its UTC boundaries from the chosen zone first. Don't mix local labels with unconverted UTC filters.
Use GENERATE_SERIES on SQL Server 2022 or later with database compatibility level 160 or higher. Check the level before running the generation block. In my test, the block ran at levels 160 and 170. At level 150 it failed with error 208, Invalid object name 'GENERATE_SERIES'.
SELECT compatibility_level
FROM sys.databases WHERE database_id = DB_ID();Changing compatibility level affects more than one report. Review that change separately if required. The alternative digit-based query later avoids relying on GENERATE_SERIES.
Create One Row for Every Requested Month
The example range begins in January and ends at April's opening. Its excluded end is a month boundary. Reject inputs that don't follow that contract.
Specify a positive step when generating the offsets. That prevents an unintended descending series for an invalid range. Validation still comes first, before calling the function.
DECLARE @StartMonth date = '2025-01-01';
DECLARE @EndExclusive date = '2025-04-01';
IF @StartMonth IS NULL OR @EndExclusive IS NULL
OR DAY(@StartMonth) <> 1 OR DAY(@EndExclusive) <> 1
OR @EndExclusive <= @StartMonth
THROW 51050, 'Use increasing, non-NULL month-start boundaries.', 1;
DECLARE @MonthCount int = DATEDIFF(month, @StartMonth, @EndExclusive);
CREATE TABLE #Months (MonthStart date NOT NULL PRIMARY KEY);
INSERT #Months (MonthStart)
SELECT DATEADD(month, value, @StartMonth)
FROM GENERATE_SERIES(0, @MonthCount - 1, 1);
SELECT MonthStart FROM #Months ORDER BY MonthStart;The primary key prevents duplicate month rows from multiplying later results. Inspect the generated boundaries before joining counts. Every included month should have one scaffold row.
Month-start anchors also avoid month-end adjustment surprises. Adding months from January's first day produces other first days. No special February branch is needed.
For an interactive report, set an approved maximum horizon. A caller requesting every supported date can create unnecessary output. Validate the requested range before generating the scaffold.
Join Real Counts to Get Zero-Filled Gaps
The fixture includes events in two months and leaves the middle month empty. Categories come from an independent table. That table includes a category with no recorded events anywhere in the range.
CREATE TABLE #Categories
(
CategoryId int NOT NULL PRIMARY KEY,
CategoryName nvarchar(50) NOT NULL
);
CREATE TABLE #MonthlyEvents
(
EventId int NOT NULL PRIMARY KEY,
CategoryId int NOT NULL,
RecordedUtc datetime2(7) NOT NULL
);
CREATE INDEX IX_MonthlyEvents_Utc
ON #MonthlyEvents (RecordedUtc) INCLUDE (CategoryId);
INSERT #Categories VALUES (1, N'Operations'), (2, N'Support'), (3, N'Billing');
INSERT #MonthlyEvents VALUES
(1, 1, '2025-01-04T09:00:00'),
(2, 2, '2025-01-20T15:00:00'),
(3, 1, '2025-03-08T11:00:00'),
(4, 2, '2025-04-01T00:00:00');
DECLARE @StartMonth date = '2025-01-01';
DECLARE @EndExclusive date = '2025-04-01';
;WITH Counts AS
(
SELECT DATEFROMPARTS(YEAR(RecordedUtc), MONTH(RecordedUtc), 1) AS MonthStart,
COUNT_BIG(*) AS EventCount
FROM #MonthlyEvents
WHERE RecordedUtc >= @StartMonth AND RecordedUtc < @EndExclusive
GROUP BY DATEFROMPARTS(YEAR(RecordedUtc), MONTH(RecordedUtc), 1)
)
SELECT m.MonthStart, COALESCE(c.EventCount, CONVERT(bigint, 0)) AS EventCount
FROM #Months AS m
LEFT JOIN Counts AS c ON c.MonthStart = m.MonthStart
ORDER BY m.MonthStart;In my run, January showed 2, February 0, and March 1, while the April 1 event stayed out. The timestamp filter remains a half-open range on the stored column. The grouping expression runs on the qualifying rows. That keeps boundary selection separate from monthly classification.
COALESCE replaces a missing joined count with zero. COUNT_BIG keeps the aggregate result in bigint. Match that type in the replacement value rather than relying on a display conversion.
For zero-filled gaps, the scaffold must remain on the preserved side of the LEFT JOIN. Starting from Counts loses the empty months again. Join direction carries the report's coverage requirement.
Place fact filters inside the counts query. A later WHERE condition against the nullable count side can remove empty scaffold rows. Inspect that risk whenever another report filter is added.
Zero-Filled Gaps for Every Month and Category
A per-category report needs every requested month-category pair. Form those pairs from the month scaffold and approved category list. Then attach counts grouped by both keys.
DECLARE @StartMonth date = '2025-01-01';
DECLARE @EndExclusive date = '2025-04-01';
;WITH Counts AS
(
SELECT DATEFROMPARTS(YEAR(RecordedUtc), MONTH(RecordedUtc), 1) AS MonthStart,
CategoryId, COUNT_BIG(*) AS EventCount
FROM #MonthlyEvents
WHERE RecordedUtc >= @StartMonth AND RecordedUtc < @EndExclusive
GROUP BY DATEFROMPARTS(YEAR(RecordedUtc), MONTH(RecordedUtc), 1), CategoryId
)
SELECT m.MonthStart, k.CategoryId, k.CategoryName,
COALESCE(c.EventCount, CONVERT(bigint, 0)) AS EventCount
FROM #Months AS m
CROSS JOIN #Categories AS k
LEFT JOIN Counts AS c
ON c.MonthStart = m.MonthStart AND c.CategoryId = k.CategoryId
ORDER BY m.MonthStart, k.CategoryId;Deriving the category list from events would hide the always-empty category. Use the business's approved list instead. Categories with effective dates need a date-aware rule for when their scaffold rows should exist.
A large cross join creates a large report even with few facts. Estimate the requested combinations before execution. Filter the category list to the authorized reporting scope first.
Validate that every fact category maps to the approved dimension. Unknown categories need a visible exception or an approved unknown bucket. Silently dropping them produces clean-looking totals that don't reconcile.
Use a Bounded Numbers Source When Needed
A numbers table supplies the same offsets without GENERATE_SERIES. This demonstration builds offsets from three sets of digits. Its maximum supported range is one thousand months.
DECLARE @StartMonth date = '2025-01-01';
DECLARE @EndExclusive date = '2025-04-01';
IF @StartMonth IS NULL OR @EndExclusive IS NULL
OR DAY(@StartMonth) <> 1 OR DAY(@EndExclusive) <> 1
OR @EndExclusive <= @StartMonth
THROW 51051, 'Use increasing month-start boundaries.', 1;
DECLARE @MonthCount int = DATEDIFF(month, @StartMonth, @EndExclusive);
IF @MonthCount > 1000
THROW 51052, 'This demonstration supports at most one thousand months.', 1;
;WITH Digits AS
(
SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)
), Numbers AS
(
SELECT a.n + 10 * b.n + 100 * c.n AS n
FROM Digits AS a CROSS JOIN Digits AS b CROSS JOIN Digits AS c
)
SELECT DATEADD(month, n, @StartMonth) AS MonthStart
FROM Numbers WHERE n < @MonthCount
ORDER BY n;Use this result to populate the month scaffold instead of the earlier generator. The rest of the joins stay the same. Keep the numbers source's maximum coverage visible in validation.
A permanent calendar or numbers table is also a practical choice for repeated reports. Give it a clear key and maintained coverage. Don't borrow row counts from an unrelated system table.
Distinguish Zero Activity From Missing Data
An empty month means zero only when the input period is known to be complete. A failed import can also produce no rows. Check ingestion completeness before interpreting the chart.
I keep feed status beside monthly totals when delayed data is possible. That prevents a zero from becoming an accidental claim about business activity. The jar is empty, but the delivery still matters.
Compare category totals with the overall monthly totals. Both should reconcile under the same scope and boundaries. A mismatch points to missing category mappings or different filter definitions.
For a category starting halfway through the range, decide whether earlier periods should show zero or remain outside scope. Those choices express different business meanings. Store effective dates if the report distinguishes them.
Keep month labels derived from MonthStart rather than sorting formatted text. Text sorting can place months alphabetically. The date key preserves chronological order regardless of the display format.
Verify the expected scaffold size from the selected months and approved categories. Compare it with the emitted result count. That check exposes duplicate dimension rows or filters that removed empty combinations.
For ratios, compute the denominator separately from the displayed zero. A zero numerator with an absent denominator doesn't establish a valid rate. Carry an undefined status when the calculation lacks the required population.
Verify the Edges and the Empty Category
Which periods and categories must appear even without activity? Turn that answer into the scaffold's contract. Test the middle empty month and the category absent from all facts.
Keep zero-filled gaps tied to known complete input and approved dimensions. Include a fact exactly at the excluded end in your tests. It belongs to the next report interval, not both.
Related reading on this blog: A WHERE Filter That Turns Your LEFT JOIN Into an INNER JOIN and SQL SERVER 2022: GENERATE_SERIES Function.
An empty reporting period is not an absent label, it is a scaffold row with a verified zero.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
