The Problem: Missing Dates in Daily Reports

I often need to produce a daily sales summary where the business expects to see a row for every calendar day, even when there were no transactions. The source table only contains rows for days with activity, so a straightforward GROUP BY sale_date leaves holes in the output. Those gaps make trend charts look misleading and force downstream consumers to handle missing data themselves.

Building a Calendar CTE

The trick is to generate a continuous series of dates and then left‑join the activity data onto it. In PostgreSQL we can use the set‑returning function generate_series inside a common table expression (CTE). This creates a temporary calendar that spans the period we care about.

-- Define the date range we want to report on
WITH params AS (
    SELECT
        DATE '2024-01-01' AS start_date,
        DATE '2024-01-31' AS end_date
),
-- Generate one row per day in the range
calendar AS (
    SELECT generate_series(start_date, end_date, interval '1 day')::date AS report_date
    FROM params
)
-- Left join the actual sales data
SELECT
    c.report_date,
    COALESCE(SUM(s.amount), 0) AS daily_total
FROM calendar c
LEFT JOIN sales s
    ON s.sale_date = c.report_date
GROUP BY c.report_date
ORDER BY c.report_date;

Why This Works

The generate_series function produces a set of timestamps (or dates) between the bounds we supply. By wrapping it in a CTE we keep the query readable and can reuse the series multiple times if needed. The left join ensures every date from the calendar appears in the result set; when there is no matching sale, the aggregates return NULL, which we turn into zero with COALESCE. This approach is declarative—SQL does the heavy lifting of filling the gaps.

Performance Considerations

Generating a series is cheap for modest date ranges (a few thousand rows). For larger spans, consider:

  • Materializing a permanent calendar table and indexing the date column.
  • Filtering the series to only the needed range via the params CTE.
  • Ensuring the sale_date column is indexed so the join is efficient.

If you are on SQL Server, replace generate_series with a recursive CTE or a built‑in tally table. The concept stays identical.

Adapting the Technique

This pattern isn’t limited to sales. I’ve used it for:

  • Website traffic reports where some hours have zero hits.
  • Inventory snapshots that need to show on‑hand quantity for every day, even when no movement occurred.
  • Service‑level dashboards that must display uptime percentages per minute.

In each case, the core steps are:

  1. Define the time grain (day, hour, minute).
  2. Generate a complete series for that grain.
  3. Left join the factual data.
  4. Aggregate and replace missing values with a sensible default (zero, last known value, etc.).

Remember: the goal is not just to produce a prettier report. A complete time series makes downstream calculations—like moving averages or year‑over‑year growth—reliable because the denominator is always known.

Final Thoughts

When you notice missing periods in your query results, resist the urge to patch the data in the application layer. A simple calendar CTE combined with a left join gives you a set‑based, efficient solution that works across most modern SQL dialects. Once you have the pattern in your toolbox, you’ll find yourself reaching for it whenever a report needs to show “every day, even the quiet ones.”