53 lines
3.4 KiB
Markdown
53 lines
3.4 KiB
Markdown
|
```sql
|
||
|
|
|
||
|
|
|
||
|
|
with tbl as (
|
||
|
|
select 'A' as flt_se, b.DSTRCT_TY, a.rwy_id
|
||
|
|
, a.EXP_APN_IN_DT as exp_apn_inout_dt
|
||
|
|
, a.ACTL_APN_IN_DT as actl_apn_inout_dt
|
||
|
|
, a.EXP_STAND_ON_DT as exp_stand_onoff_dt
|
||
|
|
, a.ACTL_STAND_ON_DT as actl_stand_onoff_dt
|
||
|
|
from uic.V_FLT_ARR a, UCM.SACP_STAND b
|
||
|
|
where a.STAND_NO = b.stand_no
|
||
|
|
and a.flt_de = to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) , 'yyyymmdd')
|
||
|
|
and b.dstrct_ty not in ('AF02005', 'AF02006')
|
||
|
|
and nvl(ac_ty_iata,'.') like :ar_acTy
|
||
|
|
and (:ar_grad = '%' or instr(:ar_grad||',', nvl(ac_grad,'.')||',') > 0 )
|
||
|
|
union all
|
||
|
|
select 'D' as flt_se, b.DSTRCT_TY, a.rwy_id
|
||
|
|
, a.EXP_APN_OUT_DT as exp_apn_inout_dt
|
||
|
|
, a.ACTL_APN_OUT_DT as actl_apn_inout_dt
|
||
|
|
, a.EXP_STAND_OFF_DT as exp_stand_on_dt
|
||
|
|
, a.ACTL_STAND_OFF_DT as actl_stand_on_dt
|
||
|
|
from uic.V_FLT_DEP a, UCM.SACP_STAND b
|
||
|
|
where a.STAND_NO = b.stand_no
|
||
|
|
and a.flt_de = to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) , 'yyyymmdd')
|
||
|
|
and b.dstrct_ty not in ('AF02005', 'AF02006')
|
||
|
|
and nvl(ac_ty_iata,'.') like :ar_acTy
|
||
|
|
and (:ar_grad = '%' or instr(:ar_grad||',', nvl(ac_grad,'.')||',') > 0 )
|
||
|
|
) -- with tbl as END
|
||
|
|
select EXP_STAND_ONOFF_DT
|
||
|
|
, case when flt_se = 'A' and actl_stand_onoff_dt <= to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) , 'yyyymmddhh24mi') then 1 else 0 end as arr_sum
|
||
|
|
, case when flt_se = 'D' and actl_stand_onoff_dt <= to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) , 'yyyymmddhh24mi') then 1 else 0 end as dep_sum
|
||
|
|
, case when flt_se = 'A' and EXP_APN_INOUT_DT > to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) , 'yyyymmddhh24mi')
|
||
|
|
and EXP_APN_INOUT_DT <= to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) + (60/24/60) , 'yyyymmddhh24mi')
|
||
|
|
then 1 else 0 end as arr_sum60
|
||
|
|
, case when flt_se = 'D' and EXP_STAND_ONOFF_DT >= to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) + (30/24/60), 'yyyymmddhh24mi')
|
||
|
|
and EXP_STAND_ONOFF_DT < to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) + (60/24/60), 'yyyymmddhh24mi')
|
||
|
|
then 1 else 0 end as dep_sum60
|
||
|
|
, case when flt_se = 'A' and EXP_APN_INOUT_DT >= to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) + (15/24/60) , 'yyyymmddhh24mi')
|
||
|
|
and EXP_APN_INOUT_DT < to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) + (30/24/60) , 'yyyymmddhh24mi')
|
||
|
|
then 1 else 0 end as arr_sum30
|
||
|
|
, case when flt_se = 'D' and EXP_STAND_ONOFF_DT >= to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) + (15/24/60), 'yyyymmddhh24mi')
|
||
|
|
and EXP_STAND_ONOFF_DT < to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) + (30/24/60) , 'yyyymmddhh24mi')
|
||
|
|
then 1 else 0 end as dep_sum30
|
||
|
|
, case when flt_se = 'A' and EXP_APN_INOUT_DT >= to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) , 'yyyymmddhh24mi')
|
||
|
|
and EXP_APN_INOUT_DT < to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) + (15/24/60) , 'yyyymmddhh24mi')
|
||
|
|
then 1 else 0 end as arr_sum15
|
||
|
|
, case when flt_se = 'D' and EXP_STAND_ONOFF_DT >= to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) , 'yyyymmddhh24mi')
|
||
|
|
and EXP_STAND_ONOFF_DT < to_char(to_date(:ar_stdDt, 'yyyymmddhh24mi') -(9/24) + (15/24/60) , 'yyyymmddhh24mi')
|
||
|
|
then 1 else 0 end as dep_sum15
|
||
|
|
from tbl where flt_se = 'D'
|
||
|
|
order by EXP_STAND_ONOFF_DT
|
||
|
|
|
||
|
|
```
|