Developer ToolsFree generatorPDF, Excel, Word exportJob Shop Manufacturing

Job Shop WIP and Late Jobs SQL Query Generator

Most small shops have an ERP database but no good way to ask it simple questions. This generator builds three practical queries from the table and column names you give it.

Fill in

Jobs table

Header table with one row per job

Operations table

One row per operation on a job

Job number column

Customer column

Part number column

Due date column

Status column

Value meaning open

Estimated hours column

Actual hours column

Generated code

Download:

Open jobs with operation hours

SELECT j.Job, j.Customer, j.Part_Number, j.Promised_Date,
       SUM(o.Est_Total_Hrs) AS est_hours,
       SUM(o.Act_Total_Hrs) AS act_hours
FROM Job j
JOIN Job_Operation o ON o.Job = j.Job
WHERE j.Status = 'Open'
GROUP BY j.Job, j.Customer, j.Part_Number, j.Promised_Date
ORDER BY j.Promised_Date;

Late open jobs

SELECT j.Job, j.Customer, j.Part_Number, j.Promised_Date,
       DATEDIFF(day, j.Promised_Date, GETDATE()) AS days_late
FROM Job j
WHERE j.Status = 'Open'
  AND j.Promised_Date < CAST(GETDATE() AS date)
ORDER BY days_late DESC;

Jobs over estimate by more than 10 percent

SELECT j.Job, j.Part_Number,
       SUM(o.Est_Total_Hrs) AS est_hours,
       SUM(o.Act_Total_Hrs) AS act_hours,
       100.0 * (SUM(o.Act_Total_Hrs) - SUM(o.Est_Total_Hrs)) / NULLIF(SUM(o.Est_Total_Hrs), 0) AS pct_over
FROM Job j
JOIN Job_Operation o ON o.Job = j.Job
GROUP BY j.Job, j.Part_Number
HAVING SUM(o.Act_Total_Hrs) > 1.10 * SUM(o.Est_Total_Hrs)
ORDER BY pct_over DESC;

Table and column names above are generic examples, not the schema of any specific product. Look up the real names in your ERP's database documentation. The date functions shown are SQL Server syntax, so adjust DATEDIFF and GETDATE for other databases. Run read-only queries against a reporting copy or with a read-only account.

Ask your ERP in plain language

See how a read-only assistant can answer these questions without writing SQL.

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

How to use it

Find the job header table and the operations table in your ERP's database documentation, then type their names and the key column names into the fields. The three blocks update immediately. Paste them into your SQL client or reporting tool and run them against a read-only account. Compare the first result to your shop floor board to check that the joins make sense.

Use a read-only login or a nightly copy of the database
Test on a few known jobs first
Check how your ERP stores closed or completed status

What each query tells you

The first query is a plain open jobs list with summed estimated and actual hours. The second lists past-due open jobs by days late. The third highlights jobs where actual hours exceed the estimate by more than ten percent, which is a quick way to find routings that are consistently under-quoted.

Common mistakes

Summing hours after joining several one-to-many tables multiplies the values, so keep the join set small. Status columns are often numeric codes, not text. Time-stamped due dates also need CAST or CONVERT before comparing to today. Always test row counts against the application.

Frequently Asked Questions

Will this work with JobBOSS or E2?

The pattern works with any relational job shop database, but table and column names differ. Replace the defaults with names from your system's data dictionary and test before relying on results.

Is it safe to run SQL against my production ERP?

Read-only SELECT queries are low risk, but heavy joins can slow down the application. Use a read-only account and ideally a reporting copy or off-hours window.

Why do my hours look doubled?

That usually means a join duplicated rows, for example joining operations and material lines in one query. Aggregate each child table separately before combining them.

Can I use this in a reporting tool?

Yes. Paste the SQL into Power BI, SSRS, Excel Power Query or any tool that accepts a custom query, and adjust date syntax to match your database.

If you want to ask these questions in plain language instead of writing SQL, Netray can connect a read-only assistant to your ERP database and show the generated query.

Talk to an Expert Explore More Free Tools

Related Tools

MES & Shop Floor

More Developer Tools tools

See all

Back to All Tools