SQL for Equipment Accountability

Write a SQL query to track equipment accountability by comparing government-furnished equipment checkouts, returns, and missing items across multiple exercise dates.

Use the supplied exercise, equipment, and transaction data. Treat an item as missing when it was checked out for an exercise but has not been returned for that exercise.

Output

1. One row per exercise date, including dates with no transactions.
2. Columns: exercise_date, exercise_name, checked_out_count, returned_count, missing_count, and missing_equipment_codes.
3. Show missing equipment codes as a comma-separated, alphabetically ordered string, or an empty string when none are missing.
4. Order by exercise_date ascending.

Output details

• Preserve a separate output row for every exercise record, including records sharing the same exercise_date and exercise_name; do not deduplicate them.
• Count every CHECKOUT and RETURN transaction, including multiple transactions for the same equipment and exercise.
• Compute missing_count as the sum of each equipment's positive CHECKOUT minus RETURN difference, never allowing a negative contribution.
• Include RETURN transactions even when their count exceeds the corresponding CHECKOUT count.
• Separate missing equipment codes with comma-space, using ', ' rather than a comma alone.