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.
m_pathways_expense_reports table.department_name, calculate:
actual_total as the sum of actual_amountreported_total as the sum of reported_amountdiscrepancy_amount as reported_total - actual_total50.discrepancy_amount descending.| 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 |
| 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 |
| department_name | actual_total | reported_total | discrepancy_amount |
|---|---|---|---|
| Dining | 1600.00 | 1690.00 | 90.00 |
| Athletics | 1600.00 | 1480.00 | -120.00 |
| Column | Type | Description |
|---|---|---|
| report_idPK | INT | Unique report row identifier |
| department_name | VARCHAR(100) | University of Michigan department name |
| report_month | DATE | Month represented by the expense report row |
| actual_amount | DECIMAL(10,2) | Actual recorded expense amount |
| reported_amount | DECIMAL(10,2) | Published report expense amount |
| report_status | VARCHAR(20) | Status of the report row such as published, draft, or pending |
| 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 |
| 7 | Athletics | 2024-02-01 | 100.00 | 80.00 | published |
| 8 | ITS | 2024-01-01 | 0.00 | 0.00 | published |
| 9 | Dining | 2024-03-01 | 60.00 | pending | |
| 10 | 2024-02-01 | 300.00 | 360.00 | published | |
| 1 | Facilities | 2024-02-07 | -41 | 137 | in_review |
| 2 | Ross School of Business | 2024-02-27 | 217 | 514 | escalated |
| 3 | Law School | 2024-02-06 | 860 | -227 | on_hold |
| 4 | Ross School of Business | 2024-02-09 | 1500 | -263 | reopened |
| 5 | Law School | 2024-02-03 | 610 | 384 | validated |
| 6 | International Center | 2024-01-19 | 666 | 762 | inactive |
| 7 | Enrollment Management | 2024-01-23 | 1343 | null | reopened |
| 8 | Housing | 2024-02-15 | 415 | 824 | resolved |
| 9 | Alumni Relations | 2024-02-24 | null | 446 | staged |
| 10 | School of Public Health | 2024-01-27 | 775 | 1600 | closed |
| 11 | null | 2024-01-04 | 444 | 278 | validated |
| 12 | Law School | 2023-12-31 | 1196 | 979 | reopened |
| 13 | null | 2024-02-22 | 1794 | 633 | staged |
| 14 | Michigan Medicine | 2024-01-04 | null | 940 | approved |
| 15 | Admissions | 2024-01-14 | 666 | 579 | escalated |
| 16 | Michigan Medicine | 2024-02-18 | 732 | 389 | cancelled |
| department_name | actual_total | reported_total | discrepancy_amount |
|---|---|---|---|
| null | 300.00 | 360.00 | 60.00 |
| Athletics | 1600.00 | 1480.00 | -120.00 |
