A contractor placed at 24 hours a week picks up coverage shifts in March, a project in May, and by the time anyone looks at the pay data they have averaged over 30 hours a week for most of a measurement period. Nobody decided that. It accumulated one approved timesheet at a time, in a system that was built to bill hours, not to add them up over twelve months.
The cost lands on the staffing firm, because the staffing firm is the employer of record for its W-2 contractors. Under the employer shared responsibility rules the IRS describes in Identifying Full-Time Employees, an employee who averages 30 hours a week (or 130 hours a month) over a measurement period is full-time for that purpose, and the look-back method exists precisely because variable-hour staff cannot be classified on the day they are hired. Whether your firm uses the look-back method, what your measurement and stability periods are, and what has to be offered to whom are questions for your benefits counsel and your payroll provider. They are not questions for an agent, and nothing below is legal advice.
What an agent can do is the part that is actually failing: nobody is watching the running average. This walkthrough builds that watchlist.
What the firm usually has instead
A quarterly spreadsheet, exported from payroll by whoever asks for it, showing hours by contractor for the period just closed. It answers the question after the answer stopped being useful. Three things are missing:
- Accumulation. Hours by pay period, summed across the whole measurement period, not per quarter.
- Projection. Not "where are they now" but "where do they land in week 52 if the last eight weeks repeat".
- A named human. A row on a list is not an action. Someone owns the decision and has to see it in time to make it.
Step one: the period table
Measurement periods are configuration, not code. Put them in a table so that when benefits counsel changes the start month, nobody has to edit a query.
create table measurement_periods (
id bigserial primary key,
label text not null, -- 'standard-2026'
population text not null, -- 'ongoing' | 'new-variable-hour'
measurement_start date not null,
measurement_end date not null,
admin_start date not null,
admin_end date not null,
stability_start date not null,
stability_end date not null,
threshold_hours_per_week numeric not null default 30,
source_note text not null -- who set this, when, from what document
);
The source_note column earns its place the first time somebody asks why the threshold is what it is. Write the answer down when you know it.
New hires whose hours are unpredictable sit in their own initial measurement period that starts from their own start date, so keep population and resolve a contractor to a period rather than assuming the standard one.
Step two: hours, from the source that pays
Use the hours that payroll paid, not the hours that the ATS thinks were scheduled and not the hours a client approved but has not been invoiced for. Approved-and-paid is the only figure that reconciles later.
create table contractor_hours (
contractor_id bigint not null, -- the identity-table id, not a per-system id
period_end date not null, -- pay period end
hours_paid numeric not null,
source_system text not null,
source_row_id text not null,
loaded_at timestamptz not null default now(),
primary key (contractor_id, period_end, source_system, source_row_id)
);
Two notes from doing this badly once. First, a contractor on two assignments for the same firm is one employee and their hours add; if your key is the placement, you will undercount exactly the people this watchlist exists to catch. Resolve to a person first, using the same identity table an agent needs across the ATS, timesheets and payroll. Second, keep source_row_id so a corrected pay run can be re-loaded without double-counting, because corrections happen.
Special unpaid leave and similar exclusions are a real part of the averaging rules and are firm-specific. Model them as an explicit adjustment row with a reason code rather than quietly deleting hours, so the arithmetic can be explained to an auditor later.
Step three: the running average and the projection
The query that matters ranks contractors by where they are heading, not where they have been.
with p as (
select * from measurement_periods where label = 'standard-2026'
),
hours as (
select
h.contractor_id,
sum(h.hours_paid) as hours_to_date,
sum(h.hours_paid) filter (
where h.period_end >= current_date - interval '56 days'
) as hours_last_8w
from contractor_hours h, p
where h.period_end between p.measurement_start and least(p.measurement_end, current_date)
group by h.contractor_id
)
select
hours.contractor_id,
round(hours.hours_to_date, 1) as hours_to_date,
round(hours.hours_to_date
/ greatest(extract(epoch from (least(p.measurement_end, current_date)
- p.measurement_start)) / 604800, 1), 2) as avg_week_to_date,
round(hours.hours_last_8w / 8.0, 2) as avg_week_recent,
round(
(hours.hours_to_date
+ (hours.hours_last_8w / 8.0)
* (extract(epoch from (p.measurement_end - current_date)) / 604800))
/ (extract(epoch from (p.measurement_end - p.measurement_start)) / 604800),
2) as projected_avg_week,
p.threshold_hours_per_week as threshold
from hours, p
order by projected_avg_week desc;
The projection is deliberately naive: it assumes the last eight weeks repeat until the period ends. That is wrong for a contractor whose assignment finishes in three weeks, which is why the watchlist joins the end dates you already sync from the ATS and shows them in the same row. A projection of 31.4 hours for someone finishing on the 20th of next month is a different conversation from 31.4 hours for someone on an open-ended assignment.
Band the output rather than emitting a single yes/no:
| Band | Projected average | What the agent does |
|---|---|---|
| Watch | 26.0 to 28.9 | Appears on the monthly list, no alert |
| Approaching | 29.0 to 30.9 | Alert to the named owner, with hours by pay period attached |
| Over | 31.0 and above | Alert plus a dated escalation record that stays open until closed by a person |
The bands below the threshold are the point. A firm that only learns about contractors already over 30 hours has learned it too late to do anything but react.
Step four: escalate to a person, with a record
The agent does not decide anything about benefits. It does four things: recomputes weekly, bands each contractor, notifies the owner of the band change, and logs what it sent.
create table hours_escalations (
id bigserial primary key,
contractor_id bigint not null,
period_label text not null,
band text not null,
projected_avg numeric not null,
computed_at timestamptz not null,
notified_role text not null, -- 'payroll-manager'
notified_user text not null, -- the actual named person
acknowledged_at timestamptz,
acknowledged_by text,
outcome_note text
);
Four rules keep this useful rather than noisy:
- One escalation per contractor per band change. A contractor who crosses into Approaching generates one alert, not one every week until somebody acts.
- Unacknowledged escalations age upward. After seven days it goes to the owner as well. A silent alert is the same as no alert.
- Every alert carries its arithmetic. Hours by pay period, the source system each figure came from, and the projection method. The first question back is always "where does that number come from".
- Nothing is sent to the contractor. Not a notice, not a question about their hours. Communication about benefits eligibility comes from the firm through its own process; an agent that emails contractors about it is creating a problem, not solving one.
Step five: the weekly digest
The alert handles the exception. The digest handles the trend: count by band, the five largest week-over-week movers, contractors with hours from more than one assignment this period, and anyone whose hours stopped arriving mid-period while their assignment is still open, which usually means a missing timesheet rather than an idle contractor. Send it to payroll and the operations lead every Monday. If it produces no action for a month, cut it down rather than letting people learn to ignore it.
What this does not do
It does not make a firm compliant with anything, and no software can. It does not classify anybody, choose a measurement method, generate a 1095-C, or model affordability. It puts an accurate running number, with its working shown, in front of the person whose job it is to decide, early enough to decide.
That is the same boundary as the rest of our Timesheet and Compliance Chaser work: the agent watches, computes and escalates; the human acts. If your paid-hours data lives in one system, your assignments in another and your measurement periods in a spreadsheet, contact us and name the systems. We will tell you which half is a week of work and which half is not yet worth building.