Heavy equipment availability and downtime
Site managers see machines standing still, but "not working" has four very different causes: rain, breakdowns, waiting for parts and skipped maintenance. Each needs a different fix. I used SQL to separate them and size the hours each one costs.
- Data
- Simulated: 48 units, 4 sites, 17,520 unit-days, 1,620 work orders (2025)
- Tools
- SQL (DuckDB), Python
- Methods
- PA / UA, MTBF / MTTR, Pareto, exposure-based failure rates
- Background
- Modelled on the fleet data I worked with at a heavy-equipment company
Fleet data is confidential, so this dataset is simulated. It records the same fields real contractors use (hour-meters, shift hours, breakdown and PM work orders), and the analysis was written as if the causes were unknown.
Where every scheduled hour went
Two KPIs separate the problem:
- Physical availability (PA) = (scheduled − downtime) / scheduled. Is the machine ready?
- Use of availability (UA) = operating / available hours. When ready, is it used?
PA is healthy. The bigger loss is ready machines standing idle, and most of that is rain standby.
Share of 210,240 scheduled machine-hours
All units, all sites, 2025
Weather standby (13.8%) costs more than all breakdowns and maintenance combined (5.8%).
Use of availability by month and site
Each site's dip lines up with its own rainy season, while physical availability stays between 90% and 97%
UA drops from 79.5% in dry months to 62.8% in rainy months. Judging sites on one annual UA target punishes them for the weather.
What breaks, and why repairs take so long
Unplanned downtime by system
Hours, 670 breakdowns. Hover for the cumulative share.
Hydraulics cause 30.5% of downtime, and the top three systems cause 66%. Hydraulic failures also repeat within 30 days 29% of the time.
Average hours to restore a breakdown
Split into hands-on repair and waiting time
Papua's mechanics repair as fast as everyone else's. The difference is 20.7 h waiting for parts vs 5.6 h elsewhere, which is 66% of its downtime.
Skipped maintenance shows up as breakdowns
Units should be serviced every 250 hour-meter hours. Using window functions over the daily hour-meter, I marked every operating hour run past the PM due point, then compared breakdown rates per 1,000 hours.
At every site, overdue hours fail 2.6–3.5× more often. Sulawesi completes only 63% of services on time, so 17% of its operating hours run overdue.
Breakdowns per 1,000 operating hours
On schedule vs past PM due. Site label shows on-time PM compliance.
WITH seq AS (
SELECT unit_id, site, model, system, start_time,
LAG(end_time) OVER (PARTITION BY unit_id, system ORDER BY start_time) AS prev_end
FROM events WHERE event_type = 'Breakdown'
)
SELECT system,
COUNT(*) AS breakdowns,
SUM(CASE WHEN DATE_DIFF('day', prev_end, start_time) <= 30 THEN 1 ELSE 0 END) AS repeat_within_30d,
ROUND(100.0 * AVG(CASE WHEN DATE_DIFF('day', prev_end, start_time) <= 30 THEN 1 ELSE 0 END), 1) AS repeat_pct
FROM seq
GROUP BY system
ORDER BY repeat_pct DESC;Bad actors
| # | Unit | Site | Model | Breakdowns | Downtime h |
|---|
6 of the top 7 units are at Papua: the parts-wait problem, seen unit by unit.
Recommendations
Stock critical spares at Papua
Consignment stock or min/max levels for hydraulic and engine parts. Bringing parts wait to the other sites' average recovers about 2,170 machine-hours a year.
Enforce PM scheduling at Sulawesi
Lock units out at due hour + 25 h and publish a weekly compliance report. Removing overdue running avoids about 45 breakdowns and 750 downtime hours a year.
Run a hydraulic RCA programme
Review hose and seal specs and operator practices. Track the 30-day repeat-failure rate monthly to see whether repairs fix the root cause.
Plan the rainy season
Move PM, training and component change-outs into rainy months, and set seasonal UA targets so sites are judged fairly.