Check how a number in your report is calculated

Record one number

Illustrative sample data: two reports read the same four service requests and both return 2. The requirement is to count requests currently marked Open. The new report reads the previous-status field instead, so it counts the wrong requests.

NULL means no current status is recorded. The SQL setup loads the CSV's empty current-status field as NULL.

Completed example: two reports, one comparison

Number, purpose, and scope

Count service requests currently marked Open. Both reports start with the same four saved records, with one row per unique request. Both return 2. No dates, joins, or permission filters are modeled.

Rule in plain language

Count a request when its current status is Open. The new report counts a request when its earlier recorded status was Open, which is a different test.

Where the rule lives

current-report.sql and replacement-report.sql, lines 1–3 in each. Both query sample_requests, populated by sample-fixture.sql. These are illustrative files; only the status field differs between the queries.

Expected and actual result

Expected: 2, including REQ-1042 and REQ-1043 once each. The existing query matches. The new report also returns 2, but includes REQ-1042 and REQ-1045. It leaves out REQ-1043 and counts REQ-1045 instead. The total matches, but the included requests do not.

What is still unknown

REQ-1045 has no current status recorded. Its true current status and the reason for the missing value remain unknown. Its previous Open value does not resolve either question. Whether to add a missing-status indicator is a separate pending decision.

Decision and acceptance

The candidate fails the stated requirement. Use current status and rerun: count 2, with REQ-1042 and REQ-1043 once each. Separately, a source-data owner must investigate REQ-1045; a report owner must decide whether to add a missing-status indicator. In a real review, document who approved the decision and when.

Example sources: the sample CSV and SQL setup. Run the setup once in a scratch SQLite session, then run both queries against those same records. The setup explicitly loads the empty current-status field as NULL. Sample file notes and metadata explain the fields and expected results.

Existing report: current-report.sql, lines 1–3.

SELECT COUNT(*) AS open_requests
FROM sample_requests
WHERE current_status = 'Open';

New report, wrong field: replacement-report.sql, lines 1–3.

SELECT COUNT(*) AS open_requests
FROM sample_requests
WHERE previous_status = 'Open';

Both results are 2. The existing report includes REQ-1042 and REQ-1043; the new report includes REQ-1042 and REQ-1045. This checks one illustrative mapping error. It does not validate a full report or establish REQ-1045’s true current status.