📅 JOIN with Missing Dates? Fill the Gaps
Sales report missing dates = misleading. Generate series creates sequences. Fill missing dates with zero sales.
📝 PostgreSQL generate_series
-- Generate numbers 1 to 100
SELECT generate_series(1, 100);
-- Generate dates
SELECT generate_series(
'2024-01-01'::date,
'2024-12-31'::date,
'1 day'::interval
);
-- Generate with step
SELECT generate_series(0, 100, 10); -- 0,10,20,...,100
-- Fill missing sales data
WITH date_range AS (
SELECT generate_series(
'2024-01-01'::date,
'2024-12-31'::date,
'1 day'
) AS sale_date
)
SELECT
dr.sale_date,
COALESCE(SUM(s.amount), 0) AS total_sales
FROM date_range dr
LEFT JOIN sales s ON dr.sale_date = s.sale_date
GROUP BY dr.sale_date
ORDER BY dr.sale_date;
🎯 MySQL/MariaDB (Recursive CTE)
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT n FROM numbers;
-- Dates in MySQL
WITH RECURSIVE dates AS (
SELECT DATE('2024-01-01') AS date
UNION ALL
SELECT date + INTERVAL 1 DAY
FROM dates
WHERE date < '2024-12-31'
)
SELECT date FROM dates;
💡 Use Cases
- Sales reports with zero-sales days included
- Calendar tables for date dimension
- Time series analysis
- Generate test data (numbers, dates, timestamps)
"Sales chart looked wrong. Days with no sales were missing. generate_series filled gaps. Chart now shows zeros on missing days. Data representation is accurate."
