SQL Generator for Receipts Missing a Mill Test Certificate

SELECT r.receipt_no,
       r.po_number,
       r.item_code,
       r.heat_number,
       r.vendor_code,
       r.receipt_date,
       DATEDIFF(day, r.receipt_date, GETDATE()) AS days_open
FROM dbo.po_receipt_line r
JOIN dbo.item_master i ON i.item_code = r.item_code
LEFT JOIN dbo.material_certificate c
       ON c.item_code = r.item_code
      AND c.heat_number = r.heat_number
      AND c.cert_type = '3.1'
WHERE i.cert_required = 1
  AND r.receipt_date >= '2026-01-01'
  AND c.heat_number IS NULL
  AND DATEDIFF(day, r.receipt_date, GETDATE()) > 5
ORDER BY days_open DESC;

SELECT r.vendor_code,
       COUNT(*) AS lines_without_cert,
       MIN(r.receipt_date) AS oldest_receipt
FROM dbo.po_receipt_line r
JOIN dbo.item_master i ON i.item_code = r.item_code
LEFT JOIN dbo.material_certificate c
       ON c.item_code = r.item_code
      AND c.heat_number = r.heat_number
      AND c.cert_type = '3.1'
WHERE i.cert_required = 1
  AND r.receipt_date >= '2026-01-01'
  AND c.heat_number IS NULL
  AND DATEDIFF(day, r.receipt_date, GETDATE()) > 5
GROUP BY r.vendor_code
ORDER BY lines_without_cert DESC;