SELECT
  t.id AS trip_id,
  t.work_order_id AS work_order_id,
  t.start_date AS start_date,
  t.end_date AS end_date,
  t.deleted_at AS deleted_at,
  CASE
    WHEN MIN(tts.extended_status_code) IS NOT NULL THEN MIN(tts.extended_status_code)
    ELSE
      CASE
        WHEN t.start_date IS NOT NULL AND (NOW() at time zone 'utc') >= (t.start_date - INTERVAL '24' HOUR) THEN 0
        ELSE 1
      END
  END as extended_status_code
FROM trips t LEFT OUTER JOIN technician_trip_statuses tts ON tts.trip_id = t.id AND tts.deleted_at IS NULL
GROUP BY t.id
