Task
TradeFlow Logistics wants a quick validation check on data entered into its drawback accounting system. Write a SQL query to identify drawback claims where the entered totals do not match the expected totals based on line-item amounts.
Requirements
- For each
claim_id, calculate the total of entered_amount from all rows with entry_status = 'submitted'.
- Return only claims where that submitted total is different from
expected_claim_amount.
- Show the results ordered by
claim_id.
Table Definition
Table: drawback_entries
| Column | Type | Description |
|---|
| entry_id | INT | Unique row identifier |
| claim_id | VARCHAR(20) | Drawback claim identifier |
| entry_date | DATE | Date the row was entered |
| entered_amount | DECIMAL(10,2) | Amount entered for the line item |
| expected_claim_amount | DECIMAL(10,2) | Expected total amount for the full claim |
| entry_status | VARCHAR(20) | Status of the row such as draft, submitted, or rejected |
| entered_by | VARCHAR(50) | User who entered the row |
Sample Data
| entry_id | claim_id | entry_date | entered_amount | expected_claim_amount | entry_status | entered_by |
|---|
| 1 | CLM-1001 | 2024-03-05 | 120.00 | 200.00 | submitted | Maya Patel |
| 2 | CLM-1002 | 2024-03-02 | 300.00 | 300.00 | submitted | Ethan Brooks |
| 3 | CLM-1001 | 2024-03-06 | 80.00 | 200.00 | submitted | Maya Patel |
| 4 | CLM-1003 | 2024-03-04 | 50.00 | 150.00 | draft | Olivia Chen |
| 5 | CLM-1004 | 2024-03-01 | 0.00 | 100.00 | submitted | Liam Carter |
| 6 | CLM-1003 | 2024-03-07 | 100.00 | 150.00 | submitted | Olivia Chen |
| 7 | CLM-1005 | 2024-03-03 | NULL | 90.00 | submitted | Noah Rivera |
| 8 | CLM-1004 | 2024-03-08 | 100.00 | 100.00 | rejected | Liam Carter |
| 9 | CLM-1006 | 2024-03-09 | 60.00 | 120.00 | submitted | Ava Singh |
| 10 | CLM-1006 | 2024-03-10 | 50.00 | 120.00 | submitted | Ava Singh |
Expected Output
| claim_id | submitted_total | expected_claim_amount |
|---|
| CLM-1003 | 100.00 | 150.00 |
| CLM-1004 | 0.00 | 100.00 |
| CLM-1005 | NULL | 90.00 |
| CLM-1006 | 110.00 | 120.00 |
