You have a monthly task to complete and notice that there are gaps in the reconciliation of the data. You are expected to report on the tasks within one week. Tell me what you would do in the period between discovering there was data missing and the task due date?
Using the supplied reconciliation tables, write a query that identifies unresolved gaps for the latest monthly task and provides the information needed for follow-up.
task_id, month_start, item_id, policy_id, expected_amount, source_amount, submission_status, days_from_discovery_to_due, and prior_month_status.policy_id, then item_id.| Column | Type | Description |
|---|---|---|
| task_idPK | INT | Monthly reconciliation task identifier |
| month_start | DATE | First day of the reconciliation month |
| discovered_at | DATE | Date the reconciliation issue was discovered |
| due_date | DATE | Reporting deadline |
| Column | Type | Description |
|---|---|---|
| item_idPK | INT | Expected reconciliation item identifier |
| task_id | INT | Related monthly task |
| policy_id | VARCHAR(20) | Policy identifier |
| expected_amount | DECIMAL(12,2) | Amount expected in the reconciliation |
| Column | Type | Description |
|---|---|---|
| submission_idPK | INT | Submission attempt identifier |
| item_id | INT | Related reconciliation item |
| submitted_at | DATE | Submission date |
| status | VARCHAR(20) | Submission review status |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Source transaction identifier |
| policy_id | VARCHAR(20) | Policy associated with the transaction |
| transaction_month | DATE | First day of the transaction month |
| amount | DECIMAL(12,2) | Transaction amount |