Why does a department disappear from the open-work board?

Prepare the diagnostic input

The manager needs a complete departmental board, not just a list of busy departments. Put the open-state condition on the task match. A WHERE filter after the left join removes both departments with no task and those whose tasks are all closed.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE departments(id TEXT PRIMARY KEY);
CREATE TABLE tasks(id TEXT PRIMARY KEY,department_id TEXT,status TEXT);
INSERT INTO departments VALUES ('D1'),('D2');
INSERT INTO tasks VALUES ('T1','D1','open'),('T2','D2','closed');

Reproduce the misleading result and correct it

SELECT d.id,COUNT(t.id) AS open_tasks FROM departments d
LEFT JOIN tasks t ON t.department_id=d.id
WHERE t.status='open' GROUP BY d.id ORDER BY d.id;

SELECT d.id,COUNT(t.id) AS open_tasks FROM departments d
LEFT JOIN tasks t ON t.department_id=d.id AND t.status='open'
GROUP BY d.id ORDER BY d.id;

The misleading board shows only D1. The correction shows D1 with one open task and D2 with zero. COUNT(t.id) counts matched tasks, so the retained department row does not itself become a task. This is a visibility correction, not a staffing recommendation.

Check the boundary before applying the fix

Add D3 with no historical tasks. It must still appear with zero; adding a department should not require inventing a task.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO departments VALUES ('D3');

The example assumes one owning department per task. Shared tasks require an agreed counting rule and an ownership table.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.