ajroetker

SELECT (CASE WHEN certnames.latest_report_id IS NULL THEN 'unreported'
             /* NOW() would instead be a parameterized value */
             WHEN reports.end_time < NOW() - interval '1 hour' THEN 'unresponsive'
             WHEN reports.noop = TRUE THEN 'noop'
             ELSE resport_statuses.status
        END) AS latest_report_status,
       certnames.certname
FROM certnames
LEFT JOIN reports ON certnames.latest_report_id = reports.id
LEFT JOIN report_statuses ON reports.status_id = report_statuses.id;