SQL Help

Create and fill a calendar table

Updated August 2026 · First published 2007 by David J. Lake

Group sales by day and any day with no sales simply doesn't appear — the report jumps from Tuesday to Friday as if Wednesday never happened. Charts get gap-toothed, averages lie. The fix is a calendar table: one row per date, which you LEFT JOIN your data onto so empty days show up as zeros.

CREATE TABLE calendar (
  cal_date DATE PRIMARY KEY    -- primary key = no duplicate dates, ever
);

Filling it: the MySQL 8 way (recursive CTE)

Since 8.0, generating a run of dates is four honest lines — start with one date, keep adding a day until the end:

INSERT INTO calendar (cal_date)
WITH RECURSIVE dates AS (
  SELECT DATE '2026-01-01' AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM dates
  WHERE d < DATE '2026-12-31'
)
SELECT d FROM dates;

One knob to know: recursion depth is capped by cte_max_recursion_depth, default 1000. A year fits; a decade doesn't. For a big range, raise it for the session first:

SET SESSION cte_max_recursion_depth = 10000;   -- ~27 years of days

Using it

SELECT c.cal_date,
       COALESCE(SUM(o.amount), 0) AS day_total
FROM calendar c
LEFT JOIN orders o ON o.order_date = c.cal_date
WHERE c.cal_date BETWEEN '2026-07-01' AND '2026-07-31'
GROUP BY c.cal_date
ORDER BY c.cal_date;

Every day of July appears, sales or not. The same table quietly powers "business days between two dates", holiday flags (add an is_holiday column), fiscal periods and the like — many warehouses treat a fat calendar table with pre-computed week/month/quarter columns as standard furniture.

The classic way: a digits table and cross joins

Before CTEs, the trick — a favourite of the old SQL forums — was to build numbers out of a ten-row digits table. It still works everywhere, and the technique generalises to generating any number sequence:

CREATE TABLE digits (i INT NOT NULL PRIMARY KEY);
INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

-- three cross joins = 10 × 10 × 10 = numbers 0..999
INSERT INTO calendar (cal_date)
SELECT DATE '2026-01-01' + INTERVAL (h.i*100 + t.i*10 + u.i) DAY
FROM digits AS h
CROSS JOIN digits AS t
CROSS JOIN digits AS u
WHERE (h.i*100 + t.i*10 + u.i) <= DATEDIFF('2026-12-31','2026-01-01');

A CROSS JOIN pairs every row of one table with every row of the other — usually an accident to avoid, here the whole point: each extra join multiplies the range by ten. The WHERE trims the overshoot so you stop exactly at the end date.