Useful queries when using the Flight Info Direct product. Change the dates or airports to match your query accordingly.
Disclaimer: These example queries have a placeholder for the Database and View name. To use these queries in your own environment, you must replace the placeholder with the correct Database and View name that has been provided E.g., SELECT * FROM "OAG_SCHEDULES".
Schedules
Show me all flights departing from Dubai on 06/06/2022.
SELECT * FROM [ViewName]
WHERE FILE_DATE = '2022-06-06'
AND DEPAPT='DXB'
AND FLIGHT_DATE='2022-06-06';
Show me all EasyJet flights departing from London Gatwick between 06/06/2022 - 12/06/2022
SELECT * FROM [ViewName]
WHERE FILE_DATE = '2022-06-06'
AND DEPAPT='LGW' AND CARRIER = 'U2'
AND FLIGHT_DATE BETWEEN '2022-06-06' AND '2022-06-12';
Status
Show me all flights that were delayed longer than 3 hours at London Luton on 1st July 2022
SELECT *, IFF(TO_DECIMAL(LEFT(ARRIVAL_ACTUAL_INGATE_VARIATION,2))>=3, TRUE, FALSE) AS DELAYED_3_HOURS FROM [ViewName]
WHERE DEPARTURE_IATA_AIRPORT_CODE = 'LTN'
AND SCHEDULED_DEPARTURE_DATE_LOCAL = '2022-07-01'
AND (ARRIVAL_ACTUAL_INGATE_TIMELINESS = 'DELAYED')
AND DELAYED_3_HOURS = 'TRUE';
Show me all cancelled flights within a week of scheduled departure from London Heathrow
SELECT * FROM [ViewName]
WHERE DEPARTURE_IATA_AIRPORT_CODE = 'LHR'
AND SCHEDULED_DEPARTURE_DATE_LOCAL >= '2022-07-04'
AND SCHEDULED_DEPARTURE_DATE_LOCAL < '2022-07-11'
AND FLIGHT_STATE = 'CANCELED'
AND IS_OPERATING_CARRIER = 'TRUE';
Seats/Capacity
Show me the total number of seats on flights landing in Melbourne from 06/06/2022 - 13/06/2022
SELECT SUM(TOTAL_SEATS) FROM [ViewName]
WHERE FILE_DATE = '2022-06-06'
AND FLIGHT_DATE BETWEEN '2022-06-06' AND '2022-06-13'
AND ARRAPT='MEL'
AND OPERATING!='N'
AND STOPS =0;
Master Data - Carrier
Show a list of all US carriers
SELECT * FROM [ViewName]
WHERE IATA_AIRLINE_DOMICILE_COUNTRY = 'UNITED STATES OF AMERICA';
Master Data - Location
Show a list of all UK Airports
SELECT * FROM [ViewName]
WHERE IATA_COUNTRY_CODE = 'GB';
Master Data - Equipment
List all aircraft manufactured by Airbus
SELECT * FROM [ViewName]
WHERE IATA_MANUFACTURER_NAME = 'Airbus';
Global Connections Data
Show me available interline connections from London Heathrow to Melbourne
SELECT * FROM [ViewName]
WHERE DEPARTURE_AIRPORT_IATA ='LHR'
AND ARRIVAL_AIRPORT_IATA ='MEL'
AND CONNECTION_TYPE ='I';
Show me all low-cost self-connect flights from London Gatwick to Los Angeles
SELECT * FROM [ViewName]
WHERE DEPARTURE_AIRPORT_IATA ='LGW'
AND ARRIVAL_AIRPORT_IATA ='LAX'
AND IS_SELF_CONNECTION=1;
Emissions Status
Show me carrier XX's total fuel burn for flights between Amsterdam and London Luton in April 2022
SELECT SUM(ESTIMATED_FUEL_BURN_TOTAL_TONNES) AS TOTAL_FUEL_BURN
FROM [ViewName]
WHERE CARRIER_CODE IN ('XX')
AND DEPARTURE_AIRPORT = 'AMS'
AND ARRIVAL_AIRPORT = 'LTN'
AND SCHEDULED_DEPARTURE_DATE BETWEEN '2022-04-01' AND '2022-04-30';
Show me the total CO2 emissions for flight number X
SELECT CARRIER_CODE,FLIGHT_NUMBER, SUM(ESTIMATED_CO2_TOTAL_TONNES) AS TOTAL_CO2_EMISSIONS FROM [ViewName]
GROUP BY CARRIER_CODE,FLIGHT_NUMBER
ORDER BY TOTAL_CO2_EMISSIONS DESC;
Compare fuel burn between two carriers for May 2022
SELECT CARRIER_CODE, SUM(ESTIMATED_FUEL_BURN_TOTAL_TONNES) AS TOTAL_FUEL_BURN FROM [ViewName]
WHERE SCHEDULED_DEPARTURE_DATE >= '2022-05-01' AND SCHEDULED_DEPARTURE_DATE <='2022-05-31'
AND CARRIER_CODE IN ('AA','DL')
AND IS_OPERATING=TRUE
AND ESTIMATED_FUEL_BURN_TOTAL_TONNES IS NOT NULL
GROUP BY CARRIER_CODE
ORDER BY TOTAL_FUEL_BURN DESC;
Show Top 10 routes with the most fuel burn for May 2022
SELECT DEPARTURE_AIRPORT,ARRIVAL_AIRPORT, SUM(ESTIMATED_FUEL_BURN_TOTAL_TONNES) AS TOTAL_FUEL_BURN FROM [ViewName]
WHERE SCHEDULED_DEPARTURE_DATE >= '2022-05-01' AND SCHEDULED_DEPARTURE_DATE <='2022-05-30'
AND IS_OPERATING=TRUE
AND ESTIMATED_FUEL_BURN_TOTAL_TONNES IS NOT NULL
GROUP BY DEPARTURE_AIRPORT,ARRIVAL_AIRPORT
ORDER BY TOTAL_FUEL_BURN DESC
LIMIT 10;
Emissions Schedules
Show the average fuel burn and CO2 emissions per flight on each route from a specific load of Emissions Schedules data
SELECT DEPARTURE_AIRPORT, ARRIVAL_AIRPORT, AVG(ESTIMATED_FUEL_BURN_TOTAL_TONNES), AVG(ESTIMATED_CO2_TOTAL_TONNES) from [ViewName]
WHERE ESTIMATED_FUEL_BURN_TOTAL_TONNES IS NOT NULL
AND IS_OPERATING = TRUE
AND LOAD_DATE ='2023-11-05'
GROUP BY 1,2 order by 1,2;
Passenger Bookings
Show the total number of bookings for each month from Dec 2023 onwards for LHR-JFK.
SELECT
TRAVEL_MONTH,
SCHEDULED_DEPARTURE_AIRPORT_IATA,
SCHEDULED_ARRIVAL_AIRPORT_IATA,
SUM(PASSENGERS_TOTAL)
FROM [ViewName]
WHERE
FILE_DATE = '2023-12-01'
AND SCHEDULED_DEPARTURE_AIRPORT_IATA = 'LHR'
AND SCHEDULED_ARRIVAL_AIRPORT_IATA = 'JFK'
GROUP BY
1,
2,
3
ORDER BY
1 ASC;
Show the total number of bookings for June each year as they were in January each year for every year in my subscription.
SELECT
FILE_DATE,
TRAVEL_MONTH,
SUM(PASSENGERS_TOTAL) AS TOTAL_PAX
FROM [ViewName]
WHERE MONTH(FILE_DATE) = 1
AND MONTH(TRAVEL_MONTH) = 6
GROUP BY 1,
2
ORDER BY 1,
2;