Task
You are given invoice data loaded into Valmont Industries reporting tables, and the finance team suspects discrepancies between source ERP invoices and the staged reporting records. Write a SQL query that identifies staged invoices with likely issues and summarizes them by vendor and invoice month. Your result should show, for each vendor and month, how many invoices were reviewed, how many have a discrepancy, and how many fall into each discrepancy type: missing ERP match, amount mismatch, date mismatch, or duplicate staged record.
Use the staged invoice table as the starting point so unmatched staged records are still included. Treat invoices as matching on invoice_number and vendor_id. A duplicate staged record means the same vendor_id and invoice_number appears more than once in staging.
Schema
| Table | Column | Type | Description |
|---|
| vendors | vendor_id | INT | Vendor identifier |
| vendors | vendor_name | VARCHAR(100) | Vendor name |
| erp_invoices | invoice_id | INT | ERP invoice row |
| erp_invoices | vendor_id | INT | Vendor identifier |
| erp_invoices | invoice_number | VARCHAR(30) | ERP invoice number |
| erp_invoices | invoice_date | DATE | ERP invoice date |
| erp_invoices | amount | DECIMAL(12,2) | ERP invoice amount |
| staged_invoices | staged_id | INT | Staged invoice row |
| staged_invoices | vendor_id | INT | Vendor identifier |
| staged_invoices | invoice_number | VARCHAR(30) | Staged invoice number |
| staged_invoices | invoice_date | DATE | Staged invoice date |
| staged_invoices | amount | DECIMAL(12,2) | Staged invoice amount |
| | | |
Sample data
| staged_id | vendor_id | invoice_number | invoice_date | amount |
|---|
| 201 | 1 | INV-1001 | 2024-01-15 | 12500.00 |
| 202 | 1 | INV-1002 | 2024-01-20 | 9800.00 |
| 205 | 2 | INV-2002 | 2024-02-01 | 7600.00 |
| 208 | 3 | INV-3001 | 2024-01-30 | 4300.00 |
| 210 | 4 | INV-4001 | 2024-02-12 | 1500.00 |
Expected output
| vendor_name | invoice_month | invoices_reviewed | discrepancy_count | missing_erp_match | amount_mismatch | date_mismatch | duplicate_staged |
|---|
| Delta Freight | 2024-02 | 2 | 2 | 0 | 0 | 1 | 1 |
| Great Plains Steel | 2024-01 | 3 | 2 | 0 | 1 | 0 | 1 |
| Prairie Coatings | 2024-01 | 2 | 1 | 1 | 0 | 0 | 0 |
