Write a SQL query to find duplicate or missing records in an operations dataset.
Use the expected_operations and operation_records tables. A duplicate is an operation identifier appearing more than once in operation_records. A missing record is an expected operation with no corresponding record.
issue_type, operation_id, and record_count.DUPLICATE or MISSING for issue_type; use zero for missing records.issue_type, then operation_id.| Column | Type | Description |
|---|---|---|
| operation_idPK | INT | Identifier of an operation expected in the dataset |
| operation_name | VARCHAR(100) | Name of the expected operation |
| required_date | DATE | Date on which the operation is expected |
| priority | VARCHAR(20) | Operational priority |
| Column | Type | Description |
|---|---|---|
| record_idPK | INT | Unique submitted record identifier |
| operation_id | INT | Referenced operation identifier |
| recorded_date | DATE | Date the operation was recorded |
| status | VARCHAR(20) | Submitted operation status |
| source_system | VARCHAR(30) | System that submitted the record |