Spreadsheets & Data

Fill Missing Days with Zero in a SQL Task-Completion Report

How do I fill missing days with zero in a DuckDB SQL task-completion report?

In DuckDB, generate an inclusive date series and LEFT JOIN task records to it by completion date. Group by each generated date and use COUNT(t.task_id), rather than COUNT(*), so dates without matching completions remain in the report with a count of zero.

WITH days AS (SELECT CAST(d AS DATE) AS day FROM generate_series(DATE '2026-01-01', DATE '2026-01-03', INTERVAL '1 day') AS s(d)) SELECT days.day, COUNT(t.task_id) AS completed_tasks FROM days LEFT JOIN tasks t ON CAST(t.completed_at AS DATE) = days.day GROUP BY days.day ORDER BY days.day;

What you need

  • A scratch database in DuckDB, PostgreSQL or MySQL 8.0 or later
  • One task row per unique, non-null task_id
  • Completion timestamps following one agreed reporting timezone

To show dates with zero completions, generate the reporting dates with generate_series, then use a LEFT JOIN to match task records to them. Group the results by date and count a non-null task identifier. If no task matches a date, the query returns a count of zero instead of omitting that date.

What does this DuckDB report count?

This report counts task rows with a non-null completion timestamp and assigns each row to the timestamp’s date. It assumes one row per task and a unique, non-null task_id. Creation dates, due dates, and open status do not affect the count.

A task created on January 1 and completed on January 3 contributes to January 3. The report uses the completion date, not the creation date or the task’s current status.

The reporting period covers January 1 through January 3, 2026, inclusive. Confirm that completion timestamps use one agreed reporting timezone before extracting their dates. The sample stores UTC timestamps in a plain TIMESTAMP column. If your data uses another convention, decide how its timestamps map to reporting dates before you compare daily totals.

Use this query for reporting, not data cleanup. It reads task rows and adds reporting dates to the result; it does not insert placeholder tasks or change completion timestamps. For a related SQL reporting pattern, see how to calculate a dashboard completion rate without averaging percentages. Find more reporting tasks in the spreadsheets and data guides.

What sample task data exposes a missing completion day?

The sample has two tasks completed on January 1, none on January 2, and one on January 3. Another task completed on January 4 falls outside the reporting period. The unfinished task has no completion date to match.

The completed_at values follow the UTC convention and use a plain DuckDB TIMESTAMP column.

task_id task_title completed_at
1001 Draft onboarding checklist 2026-01-01 09:15:00
1002 Review support handoff 2026-01-01 17:40:00
1003 Update project notes 2026-01-03 14:05:00
1004 Publish team schedule 2026-01-04 10:00:00
1005 Prepare meeting agenda NULL

Here, NULL marks an unfinished task. It does not mean the completion is unknown and should count. Leave the January 4 task and the unfinished task in the source table; neither matches a date in the reporting period.

Check the output against the sample: the two January 1 rows should yield a count of two, January 2 should appear with zero, and the January 3 row should yield a count of one. The date range contains three completed tasks.

How do you run the zero-filled daily report in DuckDB, PostgreSQL or MySQL?

Run the report by creating the sample tasks in a scratch database, generating a row for each reporting date, and left joining matching completions to those dates. The steps are written for DuckDB. PostgreSQL runs the same SQL, and MySQL needs two small changes, shown after the steps.

  1. Open a SQL connection. Use a scratch database. Do not replace an existing table named tasks.

  2. Choose the reporting timezone. The example uses UTC. The session setting below affects date extraction from TIMESTAMPTZ values, but it does not convert a plain TIMESTAMP. When plain TIMESTAMP values represent UTC, interpret them as UTC and convert them to the reporting timezone before extracting a local reporting date.

  3. Create and populate the sample table. Run this setup block once in the scratch database. Its primary key requires unique, non-null task identifiers.

SET TimeZone = 'UTC';
CREATE TABLE tasks (
    task_id INTEGER PRIMARY KEY,
    task_title VARCHAR(100) NOT NULL,
    completed_at TIMESTAMP
);

INSERT INTO tasks (task_id, task_title, completed_at)
VALUES
    (1001, 'Draft onboarding checklist', TIMESTAMP '2026-01-01 09:15:00'),
    (1002, 'Review support handoff', TIMESTAMP '2026-01-01 17:40:00'),
    (1003, 'Update project notes', TIMESTAMP '2026-01-03 14:05:00'),
    (1004, 'Publish team schedule', TIMESTAMP '2026-01-04 10:00:00'),
    (1005, 'Prepare meeting agenda', NULL);
  1. Run the reporting query. The date series creates the report rows independently of the dates present in the task records.
WITH days AS (SELECT CAST(d AS DATE) AS day FROM generate_series(DATE '2026-01-01', DATE '2026-01-03', INTERVAL '1 day') AS s(d)) SELECT days.day, COUNT(t.task_id) AS completed_tasks FROM days LEFT JOIN tasks t ON CAST(t.completed_at AS DATE) = days.day GROUP BY days.day ORDER BY days.day;
  1. Change the reporting dates for another period. Replace the two date literals inside generate_series. Keep INTERVAL '1 day' and put the earlier date first.

MySQL has no generate_series, and it names the time zone setting differently. In MySQL 8.0 or later, set the session time zone with SET time_zone = '+00:00'; instead of the SET TimeZone line, run the same table setup, and build the dates with a recursive common table expression:

WITH RECURSIVE days AS (
    SELECT DATE '2026-01-01' AS day
    UNION ALL
    SELECT day + INTERVAL 1 DAY FROM days WHERE day < DATE '2026-01-03'
)
SELECT days.day, COUNT(t.task_id) AS completed_tasks
FROM days
LEFT JOIN tasks t ON CAST(t.completed_at AS DATE) = days.day
GROUP BY days.day
ORDER BY days.day;

The recursion starts at the first date and adds one day until it reaches the last, so it plays the part of the date series. MySQL documents this pattern in its recursive common table expressions reference. The time zone conversions further down use AT TIME ZONE, which MySQL does not support; its CONVERT_TZ function does the same job.

DuckDB documents the two pieces separately: generate_series and FROM and JOIN clauses. Adjust the table name, date range, and timestamp expression to fit your data. Use a scratch database while making those changes.

What output should the January 1-3 task-completion report return?

For this sample, the report returns two completions on January 1, zero on January 2, and one on January 3.

day completed_tasks
2026-01-01 2
2026-01-02 0
2026-01-03 1

The matching task IDs account for the totals: two on January 1, none on January 2, and one on January 3. Across the reporting period, 2 + 0 + 1 = 3 completed tasks. The January 4 task falls beyond the range, and the unfinished task has a null completion timestamp.

The result still has three rows if the tasks table is empty because generate_series supplies the dates. Each date then matches zero task IDs. A zero means the query found no matching completion record for that date. It does not establish that no work happened or that the source data is complete.

Why do generate_series, LEFT JOIN, and COUNT preserve empty days?

The query preserves empty dates by generating the date rows separately, keeping them in the join, and counting only identifiers from matching task rows. The reporting dates therefore remain in the output even when no task matches.

The days common table expression calls generate_series with January 1 and January 3, 2026, using a one-day interval. It creates all three dates without requiring a stored calendar table. AS s(d) names the generated column, and CAST(d AS DATE) supplies a date-only value for matching.

The LEFT JOIN keeps every generated date, including those without a matching task. Its condition compares the date in completed_at with days.day, grouping completion times that fall on the same date. Then GROUP BY days.day returns one row for each reporting date.

COUNT(t.task_id) counts identifiers from matched task rows. When no task matches, the task-side columns contain null values, so the count is zero. In contrast, COUNT(*) counts the row retained by the join and returns one for an empty day. The sample identifiers are unique and non-null, so DISTINCT is unnecessary.

Query component Purpose
generate_series Produces daily values, including the stop date when it aligns with the interval
AS s(d) Names the generated column d
CAST(d AS DATE) Creates a date-only join key
LEFT JOIN Retains every generated day without requiring a task match
COUNT(t.task_id) Returns zero when no non-null task identifiers match
GROUP BY days.day Produces one count per date
ORDER BY days.day Ensures chronological output

The query specifies its sort order, so the result appears by date. Grouping only the task table cannot produce a date with no task rows. For another left-join example that keeps an unmatched record, see how to find projects with no tasks using a SQL LEFT JOIN.

How do you fix missing dates or incorrect counts?

If the output looks wrong, inspect the count expression, task filters, timestamp convention, and order of the reporting dates. Each can change the result, even when the date series is correct.

Empty days show one completion

Symptom: January 2 appears with completed_tasks equal to 1.

Cause: COUNT(*) counts the row retained by the left join, even when its task columns are null.

Exact fix: Replace COUNT(*) with COUNT(t.task_id). If imported task identifiers can be null, correct those values or count another field that is guaranteed to be non-null for every matching completion.

A project or team filter removes empty days

Symptom: January 2 disappears after you add a task filter.

Cause: A task-side WHERE condition rejects the null-extended rows that preserve empty dates in the result.

Exact fix: Add task-side filters to the join’s ON clause alongside the completion-date match. If your source has a project_id column, write:

LEFT JOIN tasks t
    ON CAST(t.completed_at AS DATE) = days.day
   AND t.project_id = 42

Do not add WHERE t.project_id = 42 afterward. The sample table does not have a project_id column, so this example requires one in your data.

Late-night completions appear on the wrong date

Symptom: A completion close to midnight appears on a date that conflicts with the reporting convention.

Cause: The stored timestamp convention and reporting timezone differ. Casting a timestamp to a date cannot resolve an unknown timezone convention.

Exact fix: For a TIMESTAMPTZ completion column and a report using New York dates, replace the completion-date expression in the join with:

CAST(t.completed_at AT TIME ZONE 'America/New_York' AS DATE)

For UTC values stored in a plain TIMESTAMP column, interpret them as UTC before converting them:

CAST(
    (t.completed_at AT TIME ZONE 'UTC')
    AT TIME ZONE 'America/New_York'
    AS DATE
)

Select the expression that fits the column type and storage convention, then apply the same reporting timezone to every completion. If the source timestamps already represent local time, leave them as local time rather than interpreting them as UTC.

The report returns no dates

Symptom: The query returns no rows instead of dates with zero counts.

Cause: The start date comes after the end date, but the interval is positive.

Exact fix: Put the earlier date first in generate_series. An empty task table still allows the query to generate reporting dates.

When should you avoid raw daily task counting?

Use another input or counting rule if the report should include only working days, account for repeated events, or rely on a daily aggregate. A date series fills missing dates, but it does not decide which records count as completions.

  • Working-day reports: Use a calendar table with an explicit working-day flag for weekends and organization-specific holidays. Keep the left join and generate reporting dates from that calendar.
  • Multiple events per task: Reopen events and repeated completion notifications can inflate the total if every event row counts as a completion. First derive the intended completion record for each task. Count distinct tasks only if counting each task once per reporting day fits the report’s definition.
  • Large, repeatedly queried histories: Consider maintaining a daily aggregate with the agreed reporting date and completion count. Left join reporting dates to that smaller table, then apply COALESCE to its stored count. A missing aggregate row does not count as zero by itself.

Use the same definition of completion from the source rows through the report. If the report should count completion events rather than unique tasks, document that choice and use an event-level source. If it should count each task once, reduce repeated events to the intended task record before joining to the date series.

Sources

Esc