Useful queries when using the Flight Info Direct product. Change the dates or airports to match your query accordingly.
Disclaimer: These example queries are based on our configuration. To use in your own environment, you will need to change the database to your own database. E.g. SELECT * FROM "OAG_SCHEDULES".
Schedules
Show me all flights departing from Dubai on 06/06/2022.
SELECT * FROM "METIS_DIRECT"."DIRECT_CUSTOMER_CONFIGURATIONS"."CUSTOMER_DEMO_METIS_DIRECT"
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 "METIS_DIRECT"."DIRECT_CUSTOMER_CONFIGURATIONS"."CUSTOMER_DEMO_METIS_DIRECT"
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
SELECT *, IFF(TO_DECIMAL(LEFT(ARRIVAL_ACTUAL_INGATE_VARIATION,2))>=3, TRUE, FALSE) AS DELAYED_3_HOURS FROM "METIS_DIRECT"."DIRECT_CUSTOMER_CONFIGURATIONS"."VIEW_METIS_DIRECT_FLIGHT_STATUS_LATEST"
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 "METIS_DIRECT"."DIRECT_CUSTOMER_CONFIGURATIONS"."VIEW_METIS_DIRECT_FLIGHT_STATUS_LATEST"
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
Show me the total number of seats on flights landing in Melbourne from 06/06/2022 - 13/06/2022
SELECT SUM(TOTAL_SEATS) FROM "METIS_DIRECT"."DIRECT_CUSTOMER_CONFIGURATIONS"."CUSTOMER_DEMO_METIS_DIRECT_SEATS_PLUS"
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 "METIS_DIRECT"."DIRECT_CUSTOMER_CONFIGURATIONS"."VIEW_METIS_CARRIER"
WHERE IATA_AIRLINE_DOMICILE_COUNTRY = 'UNITED STATES OF AMERICA';
Master Data - Location
Show a list of all UK Airports
SELECT * FROM "METIS_DIRECT"."DIRECT_CUSTOMER_CONFIGURATIONS"."VIEW_METIS_LOCATION"
WHERE IATA_COUNTRY_CODE = 'GB';
Master Data - Equipment
List all aircraft manufactured by Airbus
SELECT * FROM "METIS_DIRECT"."DIRECT_CUSTOMER_CONFIGURATIONS"."VIEW_METIS_EQUIPMENT"
WHERE IATA_MANUFACTURER_NAME = 'Airbus';
Global Connections Data
Show me available interline connections from London Heathrow to Melbourne
SELECT * FROM "DIRECT_CUSTOMER_CONFIGURATIONS".CUSTOMER_DEMO_METIS_DIRECT_CONNECTIONS
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 DIRECT_CUSTOMER_CONFIGURATIONS.CUSTOMER_DEMO_METIS_DIRECT_CONNECTIONS
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 "METIS_DIRECT"."DIRECT_CUSTOMER_CONFIGURATIONS"."VIEW_EMISSIONS_STATUS"
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 "METIS_DIRECT"."DIRECT_CUSTOMER_CONFIGURATIONS"."VIEW_EMISSIONS_STATUS"
WHERE SCHEDULED_DEPARTURE_DATE = '2022-05-06'
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 "METIS_DIRECT"."DIRECT_CUSTOMER_CONFIGURATIONS"."VIEW_EMISSIONS_STATUS"
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 June 2022
SELECT DEPARTURE_AIRPORT,ARRIVAL_AIRPORT, SUM(ESTIMATED_FUEL_BURN_TOTAL_TONNES) AS TOTAL_FUEL_BURN FROM "METIS_DIRECT"."DIRECT_CUSTOMER_CONFIGURATIONS"."VIEW_EMISSIONS_STATUS"
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;