Developer ToolsFree generatorPDF, Excel, Word export

Open Production Operations by Work Center SQL Query Generator

Most schedulers keep asking someone for the same three lists: remaining hours by work center, what is past due, and what is coming each week. This generator writes the SQL for those lists against your own table and column names.

Fill in

Operations table

Table holding one row per routing operation on a job or production order.

Work center column

Job or order column

Operation number column

Operation due date column

Planned hours column

Completed hours column

Status column

Status value for open

Horizon in days

Generated code

Download:

Remaining hours by work center, with past due split

SELECT
  work_center AS work_center,
  COUNT(*) AS open_operations,
  SUM(planned_hours - completed_hours) AS remaining_hours,
  SUM(CASE WHEN due_date < CURRENT_DATE THEN planned_hours - completed_hours ELSE 0 END) AS past_due_hours
FROM job_operation
WHERE status = 'RELEASED'
  AND planned_hours > completed_hours
GROUP BY work_center
ORDER BY past_due_hours DESC, remaining_hours DESC;

Past due operations list (dispatch view)

SELECT
  job_number,
  op_number,
  work_center,
  due_date,
  planned_hours - completed_hours AS remaining_hours
FROM job_operation
WHERE status = 'RELEASED'
  AND planned_hours > completed_hours
  AND due_date < CURRENT_DATE
ORDER BY due_date, work_center;

Weekly load by work center (ANSI date arithmetic, adjust to your database)

SELECT
  work_center AS work_center,
  CAST((due_date - CURRENT_DATE) / 7 AS INTEGER) AS week_offset,
  SUM(planned_hours - completed_hours) AS load_hours
FROM job_operation
WHERE status = 'RELEASED'
  AND planned_hours > completed_hours
  AND due_date <= CURRENT_DATE + 28
GROUP BY work_center, CAST((due_date - CURRENT_DATE) / 7 AS INTEGER)
ORDER BY work_center, week_offset;

Table and column names are generic placeholders because every ERP names them differently. Date subtraction and CURRENT_DATE syntax vary: SQL Server uses GETDATE() and DATEDIFF, Oracle uses SYSDATE and plain date subtraction, MySQL uses CURDATE() and DATEDIFF. Run in a read-only reporting connection and test on a copy first.

Want this answered from live ERP data?

Share your ERP and the list you rebuild every week. Optional, the generator stays free.

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

How to use the generator

Open your ERP data dictionary or ask your database administrator which table stores routing operations on released jobs. Enter that table and the column names for work center, job, operation, due date, planned hours and completed hours. Copy the three queries into your SQL client or reporting tool. If the ERP stores run and setup hours in separate columns, add them together in the expression for planned hours. Always test the result against a screen you trust, such as the ERP dispatch list.

Use a read-only reporting user or a replica.
Check the status values that mean open in your ERP.
Compare totals with one work center on screen.

What the three queries show

The first query gives the backlog per work center and splits out the hours already past due. A work center with large past due hours needs overtime, outsourcing or a re-plan this week. The second gives the operation level list a supervisor can act on. The third buckets remaining hours by week offset so you can compare it with available capacity and see which resource will be overloaded in two or three weeks, before the due dates arrive.

Making the numbers believable

Remaining hours are only as good as reported completions. If operators report quantity but not hours, derive remaining hours from remaining quantity times standard time per unit instead. Operations with status open but zero planned hours inflate counts, so filter them. Operations due in the past but waiting on a predecessor are real backlog, yet they cannot be worked, so consider splitting them from operations that are actually ready.

Common mistakes

Using the job due date for every operation puts all the load in the final week. Use operation level due dates or back-schedule them. Forgetting to exclude closed, cancelled and hold statuses overstates the load. Comparing hours with calendar capacity instead of available capacity after efficiency and downtime overstates what the work center can do. Divide the result by realistic available hours, not by shift length.

Frequently Asked Questions

Which ERP tables does this work with?

It works with any relational ERP database because you supply the table and column names. The fields default to generic names. Look up the real routing operation table in your vendor data dictionary before running anything.

Why do I get different hours than the ERP screen?

Common causes are different status filters, setup hours stored in another column, quantity based reporting, or screens that apply efficiency factors. Reconcile one work center by hand and adjust the expression for planned or completed hours.

Is it safe to run these queries on production?

They only read data, but heavy queries can slow a busy system. Use a reporting replica or run them off peak, add an index-aware filter on status, and let your DBA review them first.

Can I change the horizon?

Yes. The horizon field sets how many days ahead the weekly load query looks. Four weeks is a common scheduling window, but set it to match your frozen and planning horizons.

If you would rather ask for this in plain language, ERPray can answer it over live ERP data with the generated query visible and read-only access.

Talk to an Expert Explore More Free Tools

Related Tools

MES & Shop Floor

More Developer Tools tools

See all

Back to All Tools