# Job Shop WIP and Late Jobs SQL Query Generator

> Source: https://netray.co/tools/job-shop-wip-and-late-jobs-sql-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-to-run SQL for open jobs, late jobs and hours variance.

## Category

developer

## Industry

Job Shop Manufacturing

## Audience

- ERP administrator
- Report developer
- Shop IT

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Jobs table

**Key:** jobs_table

**Type:** text

**Default:** Job

**Help:** Header table with one row per job

#### Operations table

**Key:** ops_table

**Type:** text

**Default:** Job_Operation

**Help:** One row per operation on a job

#### Job number column

**Key:** job_id

**Type:** text

**Default:** Job

#### Customer column

**Key:** customer_col

**Type:** text

**Default:** Customer

#### Part number column

**Key:** part_col

**Type:** text

**Default:** Part_Number

#### Due date column

**Key:** due_col

**Type:** text

**Default:** Promised_Date

#### Status column

**Key:** status_col

**Type:** text

**Default:** Status

#### Value meaning open

**Key:** open_status

**Type:** text

**Default:** Open

#### Estimated hours column

**Key:** est_hours

**Type:** text

**Default:** Est_Total_Hrs

#### Actual hours column

**Key:** act_hours

**Type:** text

**Default:** Act_Total_Hrs

**Blocks:**

#### Open jobs with operation hours

**Body:** SELECT j.{{job_id}}, j.{{customer_col}}, j.{{part_col}}, j.{{due_col}},
       SUM(o.{{est_hours}}) AS est_hours,
       SUM(o.{{act_hours}}) AS act_hours
FROM {{jobs_table}} j
JOIN {{ops_table}} o ON o.{{job_id}} = j.{{job_id}}
WHERE j.{{status_col}} = '{{open_status}}'
GROUP BY j.{{job_id}}, j.{{customer_col}}, j.{{part_col}}, j.{{due_col}}
ORDER BY j.{{due_col}};

#### Late open jobs

**Body:** SELECT j.{{job_id}}, j.{{customer_col}}, j.{{part_col}}, j.{{due_col}},
       DATEDIFF(day, j.{{due_col}}, GETDATE()) AS days_late
FROM {{jobs_table}} j
WHERE j.{{status_col}} = '{{open_status}}'
  AND j.{{due_col}} < CAST(GETDATE() AS date)
ORDER BY days_late DESC;

#### Jobs over estimate by more than 10 percent

**Body:** SELECT j.{{job_id}}, j.{{part_col}},
       SUM(o.{{est_hours}}) AS est_hours,
       SUM(o.{{act_hours}}) AS act_hours,
       100.0 * (SUM(o.{{act_hours}}) - SUM(o.{{est_hours}})) / NULLIF(SUM(o.{{est_hours}}), 0) AS pct_over
FROM {{jobs_table}} j
JOIN {{ops_table}} o ON o.{{job_id}} = j.{{job_id}}
GROUP BY j.{{job_id}}, j.{{part_col}}
HAVING SUM(o.{{act_hours}}) > 1.10 * SUM(o.{{est_hours}})
ORDER BY pct_over DESC;

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

## Content

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

**Sections:**

#### How to use it

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

**Bullets:**

- 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

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

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

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

**Faq:**

#### Will this work with JobBOSS or E2?

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

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

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

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

## Search terms

job shop SQL queries, late jobs report SQL, open jobs query ERP, estimated vs actual hours SQL, JobBOSS SQL report, E2 Shop System SQL query, WIP report SQL

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