Which currently open exceptions have waited longest?

Define the decision and input records

An exception can close and reopen, so the existence of any open event is not enough. Rank events by time and a stable event ID to select the current state. This ageing policy measures from the original opening, including time spent closed; a current-open-episode report would use a different start time.

All records below are synthetic. Create these tables in a fresh SQLite database before running the read-only query. Quantities are units; monetary values are integer HKD cents, so divide by 100 when presenting HK dollars.

CREATE TABLE cases(id TEXT PRIMARY KEY, opened_at TEXT, owner_id TEXT);
CREATE TABLE case_events(id INTEGER PRIMARY KEY, case_id TEXT, occurred_at TEXT, status TEXT);
INSERT INTO cases VALUES ('E1','2026-10-01 09:00','S1'),('E2','2026-10-08 12:00',NULL),('E3','2026-09-01 09:00','S2');
INSERT INTO case_events VALUES (1,'E1','2026-10-01 09:00','open'),(2,'E1','2026-10-05 09:00','closed'),(3,'E1','2026-10-09 09:00','open'),(4,'E2','2026-10-08 12:00','open'),(5,'E3','2026-09-01 09:00','open'),(6,'E3','2026-10-02 09:00','closed');

Run the query and compare the result

WITH latest AS (
 SELECT *,ROW_NUMBER() OVER (PARTITION BY case_id ORDER BY occurred_at DESC,id DESC) AS rn
 FROM case_events WHERE occurred_at<='2026-10-10 12:00'
)
SELECT c.id,c.owner_id,
 CAST(julianday('2026-10-10 12:00')-julianday(c.opened_at) AS INTEGER) AS calendar_days
FROM cases c JOIN latest e ON e.case_id=c.id AND e.rn=1
WHERE e.status='open' ORDER BY calendar_days DESC,c.id;

E1 is currently open and nine whole calendar days old; E2 is two days old and has no owner. E3 is closed and is omitted even though it was originally older. Review E1’s history and assign E2 before applying a queue escalation rule.

Apply the result within its limits

The fixed cut-off is 10 October 2026 at 12:00; later events are excluded. Whole-day truncation is not working-hour SLA measurement. Define timezone, holidays and pauses if those affect escalation.

The supplied setup and result were executed with SQLite. Adapt the schema and status rules to a test copy of your system, then agree the expected result with the process owner. These examples have not been tested on other database platforms.