Write a SQL query to compare inventory counts against expected stock levels by location and flag discrepancies for Blue Nile.
Use the expected stock and inventory count data to evaluate the latest count for each location and SKU. Include missing counts and items found without an expected stock record.
location_code, sku, expected_stock_level, actual_count, variance, and discrepancy_status.location_code, then sku.| Column | Type | Description |
|---|---|---|
| location_codePK | VARCHAR(20) | Blue Nile location code |
| skuPK | VARCHAR(30) | Inventory keeping unit |
| expected_units | INT | Expected stock quantity for the location and SKU |
| Column | Type | Description |
|---|---|---|
| count_idPK | INT | Inventory count record identifier |
| location_code | VARCHAR(20) | Location where the inventory was counted |
| sku | VARCHAR(30) | Counted inventory keeping unit |
| counted_units | INT | Units observed during the count |
| counted_at | TIMESTAMP | Timestamp when the inventory was counted |