What Should Botie Label Each Reading?

The maintenance dashboard shows temperature readings. The team wants inspect at or above 75, monitor below that point, and unknown when no value was recorded. Botie should apply the same policy wherever the report is read.

SQL can derive that label beside each reading. We will write the conditions in an order that preserves the missing case, then check the exact threshold rather than relying on a few obvious examples.

Use a threshold-label component, then choose the reading identity and result label.

[
  {
    "reading_id": 1,
    "action": "monitor"
  },
  {
    "reading_id": 2,
    "action": "inspect"
  },
  {
    "reading_id": 3,
    "action": "unknown"
  },
  {
    "reading_id": 4,
    "action": "monitor"
  }
]

A label must mean something specific. Our threshold is an invented teaching rule, not a diagnosis of equipment health. The known temperature and the missing marker lead to different branches.

Keep the temperature in the source table. A label is a derived interpretation that may change when the maintenance policy changes. Storing only the label would discard the measurement needed to recompute it.

For a model pipeline, the policy version may matter as much as the label itself.

A searched CASE expression checks WHEN conditions and returns the value after the first matching THEN. ELSE supplies the fallback when no condition is true. Without ELSE, that fallback is NULL.

Order overlapping conditions from the intended specific case to the broader case. If we later added an emergency threshold above the inspection threshold, putting inspect first could consume readings that should have reached the emergency branch.

CASE is a decision sequence. The order of overlapping rules is part of its meaning.

A missing temperature does not satisfy temperature >= 75. If ELSE says monitor, an unrecorded reading could therefore appear to be safe to monitor. Put temperature IS NULL in its own branch.

The alias gives the resulting label a useful column name. Each input reading still produces one result row, so this operation preserves the reading grain.

A sensible default for known values can be a misleading default for absent evidence.

CREATE TABLE assets (
  asset_id TEXT PRIMARY KEY NOT NULL,
  site TEXT NOT NULL,
  kind TEXT NOT NULL,
  active INTEGER NOT NULL CHECK (active IN (0, 1))
);
CREATE TABLE inspections (
  inspection_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  minutes INTEGER CHECK (minutes >= 0),
  finding TEXT
);
CREATE TABLE readings (
  reading_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  temperature REAL
);
INSERT INTO assets VALUES
 ('P1','north','pump',1), ('P2','north','fan',1),
 ('P3','south','pump',0), ('P4','south','pump',1);
INSERT INTO inspections VALUES
 (101,'P1',30,'loose'), (102,'P1',NULL,NULL),
 (103,'P2',0,'clear'), (104,'P3',20,'clear'),
 (105,'P2',10,'loose'), (106,'P1',15,'loose');
INSERT INTO readings VALUES
 (1,'P1',70.0), (2,'P1',80.0), (3,'P2',NULL), (4,'P3',65.0);

SELECT inspection_id, COALESCE(finding, 'awaiting entry') AS finding_label
FROM inspections WHERE inspection_id <= 103 ORDER BY inspection_id;

CASE returns a single expression value. Keep its branches consistent with the meaning and type the next component expects. A column mixing a number of minutes with a text error message makes later analysis awkward and may behave differently across SQL engines.

Prefer a measured-value column and a separate status column when both are needed. COALESCE is useful for a deliberate fallback, while CASE is useful when the decision depends on a condition beyond simple missingness.

A status such as unknown should not be substituted into a numeric measurement column.

A reading of 75 must receive inspect under our at-or-above rule. A strict greater-than condition would mislabel exactly that reading while passing tests with 70 and 80.

Add a changed case at the boundary and another missing value. Those examples distinguish the actual rule from an implementation that merely agrees with the initial handful of rows.

Boundary cases reveal decisions that ordinary examples may leave ambiguous.

The SQL result is a report. Producing the word inspect does not itself schedule a technician or authorise work on a customer’s equipment. An application acting on the label needs its own permission, review and execution rules.

For analysis, also check when the reading became available. A label derived from future observations cannot be used as a feature for an earlier prediction without leakage.

A computed label is one input to a decision process, with a definition the next stage must understand.