Task
HarbourVest Partners wants a simple validation check for operational reporting in iLevel. One common control is comparing reported totals against source transaction totals before a report is published.
Write a SQL query to identify each report_date where the reported cash activity does not match the source cash activity total.
Requirements
- Use only the
ilevel_cash_activity table.
- For each
report_date, calculate:
- total
source_amount
- total
reported_amount
- the difference as
reported_amount - source_amount
- Return only dates where the totals do not match.
- Order results by
report_date ascending.
Table Definition
| column | type | description |
|---|
| activity_id | INT | Unique row identifier |
| report_date | DATE | Reporting date used in the operational report |
| fund_code | VARCHAR(20) | HarbourVest fund code |
| source_amount | DECIMAL(12,2) | Amount from the source operational record |
| reported_amount | DECIMAL(12,2) | Amount included in the published report |
| status | VARCHAR(20) | Load status for the row |
Sample Data
| activity_id | report_date | fund_code | source_amount | reported_amount | status |
|---|
| 4 | 2024-03-03 | HVPE-CORE | 200.00 | 180.00 | loaded |
| 1 | 2024-03-01 | HVPE-CORE | 1000.00 | 1000.00 | loaded |
| 7 | 2024-03-05 | HVPE-CORE | 0.00 | 0.00 | loaded |
| 2 | 2024-03-01 | HVPE-INTL | 500.00 | 500.00 | loaded |
| 9 | 2024-03-06 | HVPE-CORE | 400.00 | 400.00 | loaded |
| 3 | 2024-03-02 | HVPE-CORE | 750.00 | 750.00 | loaded |
| 10 | 2024-03-06 | HVPE-INTL | NULL | 100.00 | loaded |
| 5 | 2024-03-03 | HVPE-INTL | 300.00 | 300.00 | loaded |
| 6 | 2024-03-04 | HVPE-CORE | 600.00 | 600.00 | pending |
| 8 | 2024-03-05 | HVPE-INTL | -50.00 | -50.00 | loaded |
Expected Output
| report_date | total_source_amount | total_reported_amount | amount_difference |
|---|
| 2024-03-03 | 500.00 | 480.00 | -20.00 |
| 2024-03-06 | 400.00 | 500.00 | 100.00 |
