Task
The University of Michigan Operations team wants a quick way to spot reporting discrepancies in M-Pathways expense data. Write a SQL query to identify departments where the difference between the reported total and the actual total exceeds a small tolerance.
Requirements
- Use only the
m_pathways_expense_reports table.
- For each
department_name, calculate:
actual_total as the sum of actual_amountreported_total as the sum of reported_amountdiscrepancy_amount as reported_total - actual_total
- Return only departments where the absolute discrepancy is greater than
50.
- Sort the results by
discrepancy_amount descending.
Table Definition
| column_name | type | description |
|---|
| report_id | INT | Unique report row ID |
| department_name | VARCHAR(100) | University department name |
| report_month | DATE | Month of the report |
| actual_amount | DECIMAL(10,2) | Actual expense amount recorded |
| reported_amount | DECIMAL(10,2) | Amount shown in the published report |
| report_status | VARCHAR(20) | Status of the report row |
Sample Data
| report_id | department_name | report_month | actual_amount | reported_amount | report_status |
|---|
| 1 | Housing | 2024-01-01 | 1200.00 | 1200.00 | published |
| 2 | Dining | 2024-01-01 | 900.00 | 980.00 | published |
| 3 | Housing | 2024-02-01 | 800.00 | 790.00 | published |
| 4 | Registrar | 2024-01-01 | 500.00 | 500.00 | draft |
| 5 | Dining | 2024-02-01 | 700.00 | 650.00 | published |
| 6 | Athletics | 2024-01-01 | 1500.00 | 1400.00 | published |
Expected Output
| department_name | actual_total | reported_total | discrepancy_amount |
|---|
| Dining | 1600.00 | 1690.00 | 90.00 |
| Athletics | 1600.00 | 1480.00 | -120.00 |
