From measured hours to a coverage offer

The rule is a chain. Look back at a worker’s hours over a measurement period; if they averaged thirty a week, they are full-time for the stability period that follows and must be offered coverage; the annual filing then proves the offer, month by month, to the tax authority. Miss a link and the penalty is per employee, per month.

The hours lookback already finds who crossed the line. Benefits holds whether they were offered coverage. The compliance answer is the join between the two, and today neither page makes it. This is where they meet.

The nine are the same nine part-time workers the hours lookback found averaging above thirty. Eight were offered coverage; one was not, and that one row is the penalty exposure, findable now instead of in an audit letter. Offered plus gap equals measured, checked by the battery. Sample values are illustrative, never client data.

Join the hours fact to the enrollment fact over aligned measurement and stability periods, per worker. Full-time determination is a filter on rolling hours; the offer is a lookup on enrollment; the gap is the workers on one side and not the other. The annual filing line, was an offer made this worker this month, stops being a spreadsheet scramble and becomes a stored answer.

-- measured full-time without a coverage offer: the exposure list
SELECT m.worker_key
FROM (SELECT worker_key FROM fct_hours_measurement
      WHERE avg_weekly_hours >= 30 AND measurement_period = '2025-MSP') m
LEFT JOIN fct_enrollment_day e
  ON e.worker_key = m.worker_key
  AND e.stability_period = '2026-SP' AND e.is_offered
WHERE e.worker_key IS NULL

The left join with the null check is the whole compliance test: a worker measured full-time with no matching offer falls out as a finding, the same honesty mechanism the carrier reconciliation uses to catch a worker on one system and not the other.