# Open Production Operations by Work Center SQL Query Generator

> Source: https://netray.co/tools/open-production-operations-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your table and column names and get ready SQL for remaining hours, past due operations and weekly load per work center.

## Category

developer

## Audience

- Production scheduler
- ERP developer
- BI analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Operations table

**Key:** op_table

**Type:** text

**Default:** job_operation

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

#### Work center column

**Key:** wc_col

**Type:** text

**Default:** work_center

#### Job or order column

**Key:** job_col

**Type:** text

**Default:** job_number

#### Operation number column

**Key:** op_col

**Type:** text

**Default:** op_number

#### Operation due date column

**Key:** due_col

**Type:** text

**Default:** due_date

#### Planned hours column

**Key:** plan_col

**Type:** text

**Default:** planned_hours

#### Completed hours column

**Key:** done_col

**Type:** text

**Default:** completed_hours

#### Status column

**Key:** status_col

**Type:** text

**Default:** status

#### Status value for open

**Key:** open_status

**Type:** text

**Default:** RELEASED

#### Horizon in days

**Key:** horizon

**Type:** number

**Default:** 28

**Blocks:**

#### Remaining hours by work center, with past due split

**Body:** SELECT
  {{wc_col}} AS work_center,
  COUNT(*) AS open_operations,
  SUM({{plan_col}} - {{done_col}}) AS remaining_hours,
  SUM(CASE WHEN {{due_col}} < CURRENT_DATE THEN {{plan_col}} - {{done_col}} ELSE 0 END) AS past_due_hours
FROM {{op_table}}
WHERE {{status_col}} = '{{open_status}}'
  AND {{plan_col}} > {{done_col}}
GROUP BY {{wc_col}}
ORDER BY past_due_hours DESC, remaining_hours DESC;

#### Past due operations list (dispatch view)

**Body:** SELECT
  {{job_col}},
  {{op_col}},
  {{wc_col}},
  {{due_col}},
  {{plan_col}} - {{done_col}} AS remaining_hours
FROM {{op_table}}
WHERE {{status_col}} = '{{open_status}}'
  AND {{plan_col}} > {{done_col}}
  AND {{due_col}} < CURRENT_DATE
ORDER BY {{due_col}}, {{wc_col}};

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

**Body:** SELECT
  {{wc_col}} AS work_center,
  CAST(({{due_col}} - CURRENT_DATE) / 7 AS INTEGER) AS week_offset,
  SUM({{plan_col}} - {{done_col}}) AS load_hours
FROM {{op_table}}
WHERE {{status_col}} = '{{open_status}}'
  AND {{plan_col}} > {{done_col}}
  AND {{due_col}} <= CURRENT_DATE + {{horizon}}
GROUP BY {{wc_col}}, CAST(({{due_col}} - CURRENT_DATE) / 7 AS INTEGER)
ORDER BY {{wc_col}}, week_offset;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the generator

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Which ERP tables does this work with?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

open operations by work center sql, shop floor load query, past due production orders sql, remaining hours by work center query, production order operations sql template, work center backlog sql, scheduler load report sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json