Question numbers match their folders: folder 01 contains 01-01, 01-02 and so on. The starter dashboard uses 01-05, 01-08 and 01-02; process filter values come from 01-20. OBP identities and SQL filenames remain stable.
These English-language SQL variants preserve the source calculations and inclusion rules from the 8 November 2026 snapshot. Output aliases and display-category literals are translated into English. Replace {{schema}} with your authorized schema and run one question at a time. Dashboard and viewer filters are separate.
01-01 · Work orders by status
See the current distribution of work orders, including closed orders. This counts orders, not planned units.
Inspection hint: Groups by statusname. Other questions filter statustype; a display name is not a status code.
SELECT statusname AS "Work-order status", count(*) AS "Order count" FROM {{schema}}.workorder WHERE deletedat IS NULL GROUP BY 1 ORDER BY 2 DESC;01-02 · Open work orders: details
Find open orders, their process, planned quantity, progress and due date. Includes NEW, IN_PROGRESS and FROZEN.
Inspection hint: Missing due dates remain visible at the end. Question 01-05 excludes frozen orders; question 01-20 includes their process names.
SELECT workordernumber AS "Work order", sku AS "SKU", workflowname AS "Process", statusname AS "Status", progress AS "Progress", quantity AS "Planned quantity", expecteddate AS "Due date" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS','FROZEN') ORDER BY expecteddate NULLS LAST;01-03 · Work orders created by month
See how many orders were created in each month, regardless of their current status.
Inspection hint: Creation month is not a reconstruction of historical open backlog. A missing createdat may create a blank group.
SELECT date_trunc('month',createdat) AS "Opening month", count(*) AS "Orders" FROM {{schema}}.workorder WHERE deletedat IS NULL GROUP BY 1 ORDER BY 1;01-04 · Work orders completed by month
Count END orders with a recorded completion date, grouped by completion month.
Inspection hint: Closed orders without an end date are excluded. Use 06-03 or 01-16 to check missing dates.
SELECT date_trunc('month',"end") AS "Completion month", count(*) AS "Orders" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','')='END' AND "end" IS NOT NULL GROUP BY 1 ORDER BY 1;01-05 · Open orders by process
Compare NEW and IN_PROGRESS order counts by process. Frozen orders are excluded.
Inspection hint: Missing workflowname becomes the literal label shown in the SQL. Names may combine multiple process identities. Use 01-20 for the broader open-order dropdown.
SELECT coalesce(workflowname,'(no process)') AS "Process",count(*) AS "Open orders" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS') GROUP BY 1 ORDER BY 2 DESC;01-06 · Open orders by SKU
Compare NEW and IN_PROGRESS order counts by SKU; this is not a quantity total.
Inspection hint: Process is absent from the output. Filtering this result cannot recover a process breakdown.
SELECT sku AS "SKU",count(*) AS "Open orders" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS') GROUP BY 1 ORDER BY 2 DESC;01-07 · Age of open orders
See how long NEW and IN_PROGRESS orders have existed. Age is not days overdue.
Inspection hint: Buckets use less than 7,30 and 90 days. Missing creation dates currently fall into the oldest bucket; future dates enter the youngest bucket.
SELECT CASE WHEN date_diff('day',createdat,current_timestamp)<7 THEN '01 · Less than one week' WHEN date_diff('day',createdat,current_timestamp)<30 THEN '02 · One week to one month' WHEN date_diff('day',createdat,current_timestamp)<90 THEN '03 · One to three months' ELSE '04 · More than three months' END AS "Order age",count(*) AS "Orders" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS') GROUP BY 1 ORDER BY 1;01-08 · Overdue orders: details
Find NEW and IN_PROGRESS orders whose recorded due timestamp is already past.
Inspection hint: Excludes frozen orders and missing due dates. An order can be overdue even when the whole-day difference is zero.
SELECT workordernumber AS "Order",sku AS "SKU",workflowname AS "Process",expecteddate AS "Due date",date_diff('day',expecteddate,current_timestamp) AS "Days overdue",progress AS "Progress" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS') AND expecteddate<current_timestamp ORDER BY 5 DESC;01-09 · Open orders without a due date
Find NEW and IN_PROGRESS orders missing expecteddate so planning can be reviewed.
Inspection hint: Checks NULL only. An implausible or old date is still a populated date.
SELECT workordernumber AS "Order",sku AS "SKU",workflowname AS "Process",createdat AS "Created date",statusname AS "Status" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS') AND expecteddate IS NULL ORDER BY createdat;01-10 · On-time completion by month
Compare closed orders with both due and completion dates, showing measurable orders and the on-time percentage.
Inspection hint: Compares full timestamps. Ending exactly at the deadline is on time. Missing dates are excluded from the denominator; check 06-03.
SELECT date_trunc('month',"end") AS "Completion month",count(*) AS "Orders with dates",count_if("end"<=expecteddate) AS "Completed on time",100.0*count_if("end"<=expecteddate)/nullif(count(*),0) AS "On-time percentage" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','')='END' AND "end" IS NOT NULL AND expecteddate IS NOT NULL GROUP BY 1 ORDER BY 1;01-11 · Work-order lead time by process
Compare approximate median days from release to completion for closed orders with usable dates.
Inspection hint: Missing releasedat falls back to createdat. Whole hours are divided by 24; check how many observations used the fallback.
SELECT workflowname AS "Process",count(*) AS "Orders",approx_percentile(date_diff('hour',coalesce(releasedat,createdat),"end")/24.0,0.5) AS "Median days",count_if(releasedat IS NULL) AS "Calculated from creation" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','')='END' AND "end">=coalesce(releasedat,createdat) GROUP BY 1 ORDER BY 3 DESC;01-12 · Work orders by recorded source
Count work orders by their recorded source.
Inspection hint: NULL becomes local; an empty string does not. This does not prove an order was entered manually.
SELECT coalesce(source,'local') AS "Source",count(*) AS "Orders" FROM {{schema}}.workorder WHERE deletedat IS NULL GROUP BY 1 ORDER BY 2 DESC;01-14 · Work orders by management type
Count orders by status, serial-management flag and bulk-production flag.
Inspection hint: Missing flags remain missing; they are not automatically converted to false.
SELECT statusname AS "Status",isserial AS "Serial-managed",bulkproduction AS "Bulk production",count(*) AS "Orders" FROM {{schema}}.workorder WHERE deletedat IS NULL GROUP BY 1,2,3 ORDER BY 4 DESC;01-15 · Recorded progress in open work orders
Group open orders into missing progress, zero progress and other recorded progress.
Inspection hint: Any non-NULL nonzero value, including a negative value, enters the recorded-progress group. These are categories, not percentage bands.
SELECT CASE WHEN progress IS NULL THEN 'Not recorded' WHEN progress=0 THEN 'Zero' ELSE 'Recorded progress' END AS "Progress",count(*) AS "Orders" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS') GROUP BY 1;01-16 · Closed orders: date details
Inspect individual END orders, their dates and on-time, late or missing-information classification.
Inspection hint: Orders lacking due or completion dates stay visible as missing information. Exclude that group when comparing with 01-10.
SELECT workordernumber AS "Order",sku AS "SKU",workflowname AS "Process",expecteddate AS "Due date","end" AS "Order completion date",CASE WHEN expecteddate IS NULL OR "end" IS NULL THEN 'Missing data' WHEN "end"<=expecteddate THEN 'On time' ELSE 'Overdue' END AS "On-time classification" FROM {{schema}}.workorder WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','')='END' ORDER BY "end" DESC;01-17 · Work orders by due month
Compare orders by due month and current status.
Inspection hint: This is current status grouped by due month, not status as it was in that month. Missing due dates are excluded.
SELECT date_trunc('month',expecteddate) AS "Due month",statusname AS "Status",count(*) AS "Orders" FROM {{schema}}.workorder WHERE deletedat IS NULL AND expecteddate IS NOT NULL GROUP BY 1,2 ORDER BY 1,2;01-18 · Orders due in the next seven days
Find NEW and IN_PROGRESS orders due from execution time through the next seven 24-hour periods.
Inspection hint: A rolling window, not a shift plan. Excludes already overdue and frozen orders. A result date filter can narrow this window but cannot expand it.
SELECT workordernumber AS "Order", sku AS "SKU", workflowname AS "Process", statusname AS "Status", expecteddate AS "Due date", start AS "Order work start", quantity AS "Planned quantity"
FROM {{schema}}.workorder
WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS')
AND expecteddate>=current_timestamp AND expecteddate<current_timestamp+interval '7' day
ORDER BY expecteddate;
01-19 · Frozen work orders to review
Find individual FROZEN work orders for follow-up. Empty results are normal if none qualify.
Inspection hint: Creation is not the freeze timestamp. Freeze reason, duration and history are not available in this question.
SELECT workordernumber AS "Order", sku AS "SKU", workflowname AS "Process", statusname AS "Status", createdat AS "Frozen order creation", expecteddate AS "Due date", quantity AS "Planned quantity"
FROM {{schema}}.workorder
WHERE deletedat IS NULL AND replace(statustype,'WO.STATUSES.','')='FROZEN'
ORDER BY expecteddate NULLS LAST;
01-20 · Open-order processes: filter values
Get distinct nonblank process names from NEW, IN_PROGRESS and FROZEN orders for an open-order dashboard dropdown.
Inspection hint: Matches the status coverage of 01-02 but excludes missing or blank names. Identical names can merge different process identities. Changing the dropdown source does not change a card's calculation.
SELECT DISTINCT workflowname AS "Process"
FROM {{schema}}.workorder
WHERE deletedat IS NULL
AND replace(statustype,'WO.STATUSES.','') IN ('NEW','IN_PROGRESS','FROZEN')
AND workflowname IS NOT NULL AND trim(workflowname)<>''
ORDER BY 1;