market_segments.qmd

---
title: "Market Segments"
editor: source
---

Market segments are defined by STATFOR for the whole Agency and documentation explaining their rules and definitions can be found [here](https://www.eurocontrol.int/publication/market-segment-rules).

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.

``` code
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')
```