5  Market Segments

Market segments are defined by STATFOR for the whole Agency and documentation explaining their rules and definitions can be found here.

Below are the relevant tables to retrieve the information:

• SWH_FCT.V_FAC_FLIGHT_MS - View of the flight table containing all information at flight level, including market segment information for flights since 01/01/2004. This is the table/view you will use in most cases unless you need information prior to 2004.

• SWH_FCT.FAC_FLIGHT - Flight table containing all information at flight level, including market segment information for flights up until 31/12/2018, although to avoid mistakes, we recommend that in general you use the previous table unless you need data prior to 2004. IMPORTANT! Note that the field defining the market segment (SK_FLT_TYPE_RULE_ID) is still filled in in the table for dates after 31/12/2018, but the information is not correct and should not be used.

• SWH_FCT.DIM_FLIGHT_TYPE_RULE - Dimension table containing the names of the market segments.

• SWH_FCT.SWH_DIM_FCT.GET_FLIGHT_TYPE_RULE_SMC - Function that, when applied to each flight, yields the market segment stored in the field SK_FLT_TYPE_RULE_ID mentioned above. You can use this function, e.g. if you want data up until d-1, as the SWH_DM.DM_FL_MS contains data only up until d-2. See below example query of how to apply the function.

Example query to apply the function for d-1.

SELECT  flt_uid, 
        TRUNC (flt_a_asp_prof_time_entry) as entry_date,
        SWH_FCT.SWH_DIM_FCT.GET_FLIGHT_TYPE_RULE_SMC (flt_LOBT,
                  nvl(AO_ICAO_ID,'ZZZ'),
                  flt_acft_id,flttyp,
                  ICAO_ACFT_TY_ID,
                  flt_dep_ad,
                  flt_ctfm_ades,NVL(FLT_REG_MARKING,'ZZZ')) SK_FLT_TYPE_RULE_ID
FROM aru_flt.flt a 
WHERE flt_lobt >= trunc(sysdate) -1-1   
AND flt_lobt< trunc(sysdate) 
AND  (A.flt_a_asp_prof_time_entry) >=  trunc(sysdate) -1  
AND trunc(A.flt_a_asp_prof_time_entry) < trunc(sysdate) 
AND a.flt_state IN ('TE', 'TA', 'AA')