Which closed exceptions have since reopened?

Define the decision and input records

Start with each case’s latest event and require an earlier closed event. The chronological tie-break uses the event ID so two events at the same timestamp still have an agreed order. A case that reopened and then closed again must not appear as currently reopened.

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 e.case_id,e.occurred_at AS reopened_at FROM latest e
WHERE e.rn=1 AND e.status='open'
 AND EXISTS (SELECT 1 FROM case_events old WHERE old.case_id=e.case_id
 AND old.status='closed' AND (old.occurred_at<e.occurred_at
 OR (old.occurred_at=e.occurred_at AND old.id<e.id)))
ORDER BY e.case_id;

Only E1 is listed, with its latest open event on 9 October at 09:00. It had closed on 5 October. E2 has never closed, and E3’s latest status is closed. Compare E1’s reopened reason with the earlier resolution; reopening alone does not prove the same fault recurred.

Apply the result within its limits

The dataset has only open and closed states. Waiting, cancelled or duplicate states need an explicit transition policy. The latest open timestamp represents the current event, not necessarily the first moment of a multi-event open episode.

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.