Developer ToolsFree generatorPDF, Excel, Word exportPlastics and Injection Molding

Molding Scrap and Cycle Time SQL Query Generator

Molders often have shot, scrap and cycle data in an MES or ERP but no quick way to see which tool or press is the problem. This generator writes three generic SQL queries you can adapt to your own tables.

Fill in

Shot or production table

Generic name, replace with your MES or ERP table

Tool / mold column

Press column

Timestamp column

Cycle time (seconds) column

Good parts column

Scrap parts column

Standard cycle time (seconds)

Preventive maintenance interval (shots)

Start date

End date (exclusive)

Generated code

Download:

1. Scrap rate by tool and press

SELECT tool_id,
       press_id,
       SUM(good_parts) AS good_parts,
       SUM(scrap_parts) AS scrap_parts,
       CASE WHEN SUM(good_parts + scrap_parts) = 0 THEN 0
            ELSE 100.0 * SUM(scrap_parts) / SUM(good_parts + scrap_parts) END AS scrap_pct
FROM mold_shot_log
WHERE shot_time >= '2026-10-01'
  AND shot_time < '2026-11-01'
GROUP BY tool_id, press_id
ORDER BY scrap_pct DESC;

2. Average cycle time versus standard

SELECT tool_id,
       press_id,
       COUNT(*) AS shots,
       AVG(cycle_seconds) AS avg_cycle_seconds,
       28 AS standard_cycle_seconds,
       AVG(cycle_seconds) - 28 AS variance_seconds,
       100.0 * (AVG(cycle_seconds) - 28) / 28 AS variance_pct
FROM mold_shot_log
WHERE shot_time >= '2026-10-01'
  AND shot_time < '2026-11-01'
  AND cycle_seconds > 0
GROUP BY tool_id, press_id
ORDER BY variance_pct DESC;

3. Tools due for preventive maintenance by shot count

SELECT tool_id,
       COUNT(*) AS total_shots,
       COUNT(*) - 250000 * FLOOR(COUNT(*) / 250000) AS shots_since_last_pm,
       250000 - (COUNT(*) - 250000 * FLOOR(COUNT(*) / 250000)) AS shots_to_next_pm
FROM mold_shot_log
GROUP BY tool_id
HAVING COUNT(*) - 250000 * FLOOR(COUNT(*) / 250000) >= 0.9 * 250000
ORDER BY shots_to_next_pm ASC;

Table and column names are generic and will not match any specific MES or ERP out of the box. Replace them with your own and check date literal syntax and the FLOOR function for your database. The shot-count query assumes one row per shot and counts from the start of the table; for a real PM program use the shot count since the last recorded maintenance event. Run on a read-only copy or reporting database first.

Ask scrap and cycle questions in plain language

Optional: see how ERPray can answer these questions over your MES or ERP data, on-premises.

Optional. The tool and every export work without this. No spam.

How to use the generator

Enter your shot or production table name and the column names for tool, press, timestamp, cycle time, good parts and scrap parts. Set a standard cycle time, a preventive maintenance interval in shots, and a date range. The output shows three queries: scrap rate by tool and press, average cycle time against standard, and tools that have reached a share of their PM interval. Copy them into your SQL client and adjust the syntax for your database engine.

Use a reporting copy of the database where possible
Match column names exactly to your schema
Test with a short date range first

What each query tells you

The scrap query ranks tool and press combinations by scrap percentage so you can see whether a problem follows the tool or the machine. The cycle query compares actual average cycle time with the standard, highlighting tools that quietly run slower than costed. The shot-count query lists tools approaching their preventive maintenance interval so maintenance can plan before wear causes flash or quality loss.

Adapting it to your ERP or MES

Most plants keep shot or production data in an MES history table, a historian, or a production reporting table inside the ERP. Names and structures differ widely, so this tool stays generic. If your system stores scrap in a separate table, join it to production by order and operation. If cycle time is not stored per shot, calculate it from timestamps. Check performance on large tables, and add an index on the timestamp and tool columns.

Common mistakes

Frequent errors include dividing by zero when no parts were counted, counting warm-up shots as cycle data, using shot counts that include rejected shots without noting it, and ignoring multi-cavity tools where parts per shot differs from shots. Decide whether your counts are shots or parts, and normalize by cavity count. Also keep time zones consistent when shifts cross midnight.

Frequently Asked Questions

Will these queries run on my MES unchanged?

No. Table and column names are generic placeholders. Replace them with your own names and check date and function syntax for your database, such as SQL Server, Oracle, PostgreSQL or MySQL.

How should I count shots for multi-cavity tools?

Decide whether you track shots or parts. If your table stores parts, divide by cavities to get shots. Mixing the two skews cycle and preventive maintenance calculations, so normalize before comparing tools.

Why compare cycle time with the standard?

Costing and quotes assume a standard cycle. If the average runs longer, margin erodes unnoticed. The comparison shows which tools run slower, so you can investigate cooling, automation or process settings.

Is the PM shot count query accurate?

It is a simple approximation based on total shots in the table. For real programs, count shots since the last recorded maintenance event stored in your maintenance system, and use this query as a starting template.

If you want to ask questions like these in plain language against your ERP or MES data, with the generated query shown, Netray can help you set up ERPray on-premises.

Talk to an Expert Explore More Free Tools

Related Tools

Quality & Inspection

More Developer Tools tools

See all

Back to All Tools