Sheet Metal Job Cost Rollup SQL Query Generator

-- Generic table and column names. Map to your ERP before running.
WITH labor AS (
  SELECT o.job_no,
         SUM(o.setup_hours * wc.hourly_rate)  AS setup_cost,
         SUM(o.run_hours   * wc.hourly_rate)  AS run_cost,
         SUM(CASE WHEN wc.wc_code IN ('LASER','PUNCH') THEN o.run_hours ELSE 0 END) AS cutting_hours,
         SUM(CASE WHEN wc.wc_code = 'BRAKE' THEN o.run_hours ELSE 0 END)            AS bending_hours
  FROM fab_job_operation o
  JOIN fab_work_center wc ON wc.wc_code = o.wc_code
  GROUP BY o.job_no
),
material AS (
  SELECT m.job_no, SUM(m.qty_issued * m.unit_cost) AS material_cost
  FROM fab_job_material m
  GROUP BY m.job_no
)
SELECT j.job_no,
       j.part_no,
       j.qty_completed,
       COALESCE(mt.material_cost, 0)                              AS material_cost,
       COALESCE(l.setup_cost, 0) + COALESCE(l.run_cost, 0)       AS labor_cost,
       COALESCE(mt.material_cost, 0) + COALESCE(l.setup_cost, 0) + COALESCE(l.run_cost, 0) AS total_cost,
       CASE WHEN j.qty_completed > 0
            THEN (COALESCE(mt.material_cost, 0) + COALESCE(l.setup_cost, 0) + COALESCE(l.run_cost, 0)) / j.qty_completed
            ELSE 0 END                                            AS cost_per_part,
       l.cutting_hours,
       l.bending_hours
FROM fab_job j
LEFT JOIN labor l    ON l.job_no = j.job_no
LEFT JOIN material mt ON mt.job_no = j.job_no
WHERE j.close_date >= '2026-10-01'
  AND j.close_date <  '2026-11-01'
ORDER BY total_cost DESC;