Write a SQL query to join multiple tables, filter records based on specific operational dates, and aggregate the results.