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.
claim_id, calculate the total of entered_amount from all rows with entry_status = 'submitted'.expected_claim_amount.claim_id.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 |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| entry_idPK | INT | Unique identifier for each drawback entry row |
| claim_id | VARCHAR(20) | Drawback claim identifier |
| entry_date | DATE | Date the entry was recorded |
| entered_amount | DECIMAL(10,2) | Line-item amount entered into the drawback system |
| expected_claim_amount | DECIMAL(10,2) | Expected total amount for the entire claim |
| entry_status | VARCHAR(20) | Current status of the entry |
| entered_by | VARCHAR(50) | User who entered the record |
| 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 | 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 |
| 1 | CLM-1024 | 2024-03-07 | 305 | 198 | exported | James Walker |
| 2 | CLM-1001 | 2024-03-02 | -58 | 199 | approved | Henry Ward |
| 3 | CLM-1003 | 2024-03-02 | -39 | 313 | resubmitted | Evelyn Perry |
| 4 | CLM-1015 | 2024-03-04 | 153 | 53 | approved | null |
| 5 | CLM-1008 | 2024-03-02 | 78 | 92 | submitted | Ethan Brooks |
| 6 | CLM-1017 | 2024-03-09 | 113 | 234 | manual_review | Samuel Jenkins |
| 7 | CLM-1015 | 2024-03-09 | 225 | 167 | exported | Olivia Chen |
| 8 | CLM-1003 | 2024-03-10 | -59 | 175 | closed | Michael Ross |
| 9 | CLM-1023 | 2024-03-04 | 18 | 74 | finalized | James Walker |
| 10 | CLM-1020 | 2024-03-05 | 218 | 66 | finalized | Mia Kelly |
| 11 | CLM-1010 | 2024-03-07 | -36 | 337 | submitted | Liam Carter |
| 12 | CLM-1016 | 2024-03-08 | 186 | 284 | processed | Sophia Turner |
| 13 | CLM-1022 | 2024-03-01 | 355 | 142 | failed_check | null |
| 14 | CLM-1017 | 2024-03-07 | 45 | 268 | closed | Isabella Reed |
| 15 | CLM-1003 | 2024-03-11 | -42 | 188 | processed | Lucas Bennett |
| 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 |
| CLM-1010 | -36.00 | 337.00 |
