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.
ilevel_cash_activity table.report_date, calculate:
source_amountreported_amountreported_amount - source_amountreport_date ascending.| 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 |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| activity_idPK | 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 |
| 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 | 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 |
| 1 | 2024-03-02 | HVPE-INTL | -77 | -22 | validated |
| 2 | 2024-03-09 | HVPE-CORE | 487 | 212 | null |
| 3 | 2024-02-27 | HVPE-DIRECT1 | 908 | null | exception |
| 4 | 2024-03-09 | HVPE-CORE | -12 | 1022 | null |
| 5 | 2024-03-07 | HVPE-INFRA | 657 | 791 | rejected |
| 6 | 2024-03-09 | HVPE-USBUYOUT | 879 | 199 | escalated |
| 7 | 2024-03-08 | HVPE-DIRECT1 | 1134 | -69 | archived |
| 8 | 2024-02-29 | HVPE-FOF2 | 431 | 944 | manual_check |
| 9 | 2024-03-03 | HVPE-FOF1 | 55 | 381 | pending |
| 10 | 2024-03-06 | HVPE-DIRECT2 | 788 | -232 | cancelled |
| 11 | 2024-03-03 | HVPE-INFRA | 460 | -59 | approved |
| 12 | 2024-02-29 | HVPE-REALASSETS | 89 | 594 | pending |
| 13 | 2024-03-06 | HVPE-DIRECT2 | 48 | null | draft |
| 14 | 2024-03-06 | HVPE-LIQUIDITY | 583 | 877 | signed_off |
| 15 | 2024-03-03 | HVPE-DISTRESSED | -64 | null | rejected |
| 16 | 2024-02-29 | HVPE-PRIMARIES | 524 | 1153 | escalated |
| 17 | 2024-03-01 | HVPE-EUROPE | 430 | 276 | queued |
| 18 | 2024-02-28 | HVPE-GLOBAL | 636 | 1066 | approved |
| 19 | 2024-03-05 | HVPE-DIRECT1 | 1091 | null | staged |
| report_date | total_source_amount | total_reported_amount | amount_difference |
|---|---|---|---|
| 2024-02-28 | 636.00 | 1066.00 | 430.00 |
| 2024-02-29 | 613.00 | 1747.00 | 1134.00 |
| 2024-03-01 | 1930.00 | 1776.00 | -154.00 |
| 2024-03-03 | 896.00 | 421.00 | -475.00 |
| 2024-03-05 | 1041.00 | -50.00 | -1091.00 |
| 2024-03-06 | 1031.00 | 1377.00 | 346.00 |
