Why is a noon pickup counted in two shifts?

Prepare the diagnostic input

These reporting windows meet at noon and must allocate each pickup once. BETWEEN includes both endpoints, so noon matches two shifts. Express the agreed half-open intervals directly and retain the complete timestamp comparison rather than extracting only an hour.

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 windows(id TEXT PRIMARY KEY,start_at TEXT,end_at TEXT);
CREATE TABLE pickups(id TEXT PRIMARY KEY,picked_at TEXT);
INSERT INTO windows VALUES ('AM','2026-10-10 09:00','2026-10-10 12:00'),('PM','2026-10-10 12:00','2026-10-10 17:00');
INSERT INTO pickups VALUES ('P1','2026-10-10 11:00'),('P2','2026-10-10 12:00');

Reproduce the misleading result and correct it

SELECT w.id,COUNT(p.id) AS pickups FROM windows w LEFT JOIN pickups p
ON p.picked_at BETWEEN w.start_at AND w.end_at GROUP BY w.id ORDER BY w.id;

SELECT w.id,COUNT(p.id) AS pickups FROM windows w LEFT JOIN pickups p
ON p.picked_at>=w.start_at AND p.picked_at<w.end_at GROUP BY w.id ORDER BY w.id;

P1 at 11:00 belongs to AM, while P2 at 12:00 belongs to PM. The wrong table totals three shift allocations for two pickups. The corrected table has one per shift. This reports work allocation; it does not change an actual booking or shift roster.

Check the boundary before applying the fix

A pickup at the final 17:00 endpoint is outside both supplied windows. It should be caught by an out-of-window check rather than pulled into PM by an inclusive endpoint.

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 pickups VALUES ('P3','2026-10-10 17:00');

Overlapping or gapped configured windows need their own validation. This correction assumes the intended windows already form the stated sequence.

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.