PPAP Status Tracker SQL Query Generator

SELECT supplier_name, part_number, submission_level, due_date,
       CURRENT_DATE - due_date AS days_overdue
FROM ppap_submission
WHERE status = 'Pending'
  AND due_date < CURRENT_DATE
ORDER BY days_overdue DESC;

SELECT supplier_name,
       COUNT(*) AS open_submissions,
       MIN(due_date) AS earliest_due
FROM ppap_submission
WHERE status = 'Pending'
GROUP BY supplier_name
ORDER BY open_submissions DESC;

SELECT supplier_name, part_number, submission_level, submitted_date
FROM ppap_submission
WHERE status = 'Rejected'
ORDER BY submitted_date DESC;

SELECT supplier_name,
       COUNT(*) AS total,
       SUM(CASE WHEN status = 'Rejected' THEN 1 ELSE 0 END) AS rejected,
       100.0 * SUM(CASE WHEN status <> 'Rejected' THEN 1 ELSE 0 END) / COUNT(*) AS accepted_pct
FROM ppap_submission
WHERE submitted_date IS NOT NULL
GROUP BY supplier_name
ORDER BY accepted_pct ASC;