Skip to content

Instantly share code, notes, and snippets.

@andyfowler

andyfowler/gist:c753acb561ff337baaedd2701a9393c8

Created May 6, 2020 02:26
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save andyfowler/c753acb561ff337baaedd2701a9393c8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save andyfowler/c753acb561ff337baaedd2701a9393c8 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. andyfowler created this gist May 6, 2020.
    20 changes: 20 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,20 @@
    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;