Foundry Heat Traceability SQL Query Generator

-- Forward trace for heat H-24-08817
SELECT h.heat_no,
       h.pour_date,
       h.alloy,
       c.serial_no,
       c.part_no,
       c.status,
       l.load_no,
       l.furnace_id,
       l.cycle_name,
       l.completed_at
FROM   foundry_heat h
JOIN   casting_serial c ON c.heat_no = h.heat_no
LEFT JOIN heat_treat_load l ON l.load_no = c.load_no
WHERE  h.heat_no = 'H-24-08817'
ORDER BY c.serial_no;

-- Backward trace for serial CS-000451239
SELECT c.serial_no,
       c.part_no,
       h.heat_no,
       h.pour_date,
       h.alloy,
       k.element,
       k.result_pct,
       l.load_no,
       l.furnace_id,
       l.cycle_name
FROM   casting_serial c
JOIN   foundry_heat h ON h.heat_no = c.heat_no
LEFT JOIN heat_chemistry k ON k.heat_no = h.heat_no
LEFT JOIN heat_treat_load l ON l.load_no = c.load_no
WHERE  c.serial_no = 'CS-000451239'
ORDER BY k.element;

-- Open castings by heat, pour date between 2026-09-01 and 2026-09-30
SELECT h.heat_no,
       h.pour_date,
       COUNT(*) AS casting_count,
       SUM(CASE WHEN c.status <> 'RELEASED' THEN 1 ELSE 0 END) AS not_released
FROM   foundry_heat h
JOIN   casting_serial c ON c.heat_no = h.heat_no
WHERE  h.pour_date BETWEEN '2026-09-01' AND '2026-09-30'
  AND  h.alloy = '65-45-12 ductile iron'
GROUP BY h.heat_no, h.pour_date
HAVING SUM(CASE WHEN c.status <> 'RELEASED' THEN 1 ELSE 0 END) > 0
ORDER BY h.pour_date;