Flyers currency research

gistfile1.txt

SELECT pilot_name
, DATEDIFF(NOW(), MAX(IF(instructor_name IS NOT NULL, schedule_end, 0))) as days_since_last_dual_flight
, SUM(hobb_duration) as hours_past_year
, SUM(landings) as landings_past_year
, COUNT(id) as flights_past_year
, SUM(IF( schedule_end > (NOW() - INTERVAL 180 DAY), hobb_duration, 0)) as hours_180_days
, SUM(IF( schedule_end > (NOW() - INTERVAL 180 DAY), landings, 0)) as landings_180_days
, SUM(IF( schedule_end > (NOW() - INTERVAL 180 DAY), 1, 0)) as flights_180_days
, SUM(IF( schedule_end > (NOW() - INTERVAL 90 DAY), hobb_duration, 0)) as hours_90_days
, SUM(IF( schedule_end > (NOW() - INTERVAL 90 DAY), landings, 0)) as landings_90_days
, SUM(IF( schedule_end > (NOW() - INTERVAL 90 DAY), 1, 0)) as flights_90_days
, SUM(IF( schedule_end > (NOW() - INTERVAL 60 DAY), hobb_duration, 0)) as hours_60_days
, SUM(IF( schedule_end > (NOW() - INTERVAL 60 DAY), landings, 0)) as landings_60_days
, SUM(IF( schedule_end > (NOW() - INTERVAL 60 DAY), 1, 0)) as flights_60_days
, IF( (SUM(IF( schedule_end > (NOW() - INTERVAL 90 DAY), hobb_duration, 0)) >= 3) AND (SUM(IF( schedule_end > (NOW() - INTERVAL 90 DAY), landings, 0)) >= 9), 'yes', 'no') AS is_club_current
FROM flights

WHERE schedule_end > '2019-05-01'
GROUP BY pilot_name
ORDER BY hours_past_year DESC;