I don't know what I was thinking…

clusterfuck.sql

SELECT
CAST(COALESCE(trans_online.headcount,0) + ((COALESCE(class_online.headcount,0) - (COALESCE(class_online.headcount,0) * COALESCE(nesting.nesting_percent,0)))) AS DECIMAL(6,2)) headcount
FROM training_classes
JOIN weeks
LEFT JOIN (SELECT x.training_class_id, y.start_on online_week, z.headcount FROM (SELECT a.training_class_id, MAX(b.start_on) start_on FROM training_class_forecasts a JOIN weeks b ON a.week_id = b.id WHERE a.headcount > 0 GROUP BY a.training_class_id) x JOIN weeks y ON x.start_on = y.start_on JOIN training_class_forecasts z ON x.training_class_id = z.training_class_id AND y.id = z.week_id) class_online ON class_online.training_class_id = training_classes.id AND training_classes.transition = 0 AND class_online.online_week = weeks.start_on - INTERVAL 7 DAY
LEFT JOIN (SELECT x.training_class_id, y.start_on online_week, z.headcount FROM (SELECT a.training_class_id, MAX(b.start_on) start_on FROM training_class_forecasts a JOIN weeks b ON a.week_id = b.id WHERE a.headcount > 0 GROUP BY a.training_class_id) x JOIN weeks y ON x.start_on = y.start_on JOIN training_class_forecasts z ON x.training_class_id = z.training_class_id AND y.id = z.week_id) trans_online ON trans_online.training_class_id = training_classes.id AND training_classes.transition = 1 AND trans_online.online_week = weeks.start_on
LEFT JOIN (SELECT x.training_class_id, y.start_on online_week, z.nesting_percent FROM (SELECT a.training_class_id, MAX(b.start_on) start_on FROM training_class_forecasts a JOIN weeks b ON a.week_id = b.id WHERE a.nesting_percent > 0 GROUP BY a.training_class_id) x JOIN weeks y ON x.start_on = y.start_on JOIN training_class_forecasts z ON x.training_class_id = z.training_class_id AND y.id = z.week_id) nesting ON nesting.training_class_id = training_classes.id AND nesting.online_week = weeks.start_on