52 lines
1.6 KiB
Markdown
52 lines
1.6 KiB
Markdown
|
```sql
|
||
|
|
|
||
|
|
|
||
|
|
-- 출/도착 스케쥴
|
||
|
|
select ucm.fun_TmFormat(actl_apn_out_dt, 9) as dt, clsgn, ac_reg_no, schdul_id, flt_sttus
|
||
|
|
, nvl((select ucm.fun_get_reg_acty(ac_reg_no) from dual ), '.') as ac_ty
|
||
|
|
from uic.V_FLT_DEP a
|
||
|
|
where 1=1
|
||
|
|
and schdul_id in (select distinct schdul_id
|
||
|
|
from UCM.SACP_AREA_USE_HIST_PRCSNG
|
||
|
|
where AREA_TY = 'AF05003'
|
||
|
|
and in_ymd = '20240820'
|
||
|
|
and in_hh = :ar_hh
|
||
|
|
and schdul_id is not null)
|
||
|
|
union all
|
||
|
|
select ucm.fun_TmFormat(actl_apn_in_dt, 9) as dt, clsgn, ac_reg_no, schdul_id, flt_sttus
|
||
|
|
, nvl((select ucm.fun_get_reg_acty(ac_reg_no) from dual ), '.') as ac_ty
|
||
|
|
from uic.V_FLT_arr a
|
||
|
|
where 1=1
|
||
|
|
and schdul_id in (select distinct schdul_id
|
||
|
|
from UCM.SACP_AREA_USE_HIST_PRCSNG
|
||
|
|
where AREA_TY = 'AF05003'
|
||
|
|
and in_ymd = '20240820'
|
||
|
|
and in_hh = :ar_hh
|
||
|
|
and schdul_id is not null)
|
||
|
|
order by schdul_id
|
||
|
|
|
||
|
|
|
||
|
|
-- 시간 지연, 로미오7 관통
|
||
|
|
select m.ymd, m.hh, max(m.mi), max(m.ss), m.area_id, m.ac_ty, count(*) as use_sec, m.SCHDUL_ID
|
||
|
|
from (
|
||
|
|
select b.ymd, b.hh, b.mi, b.ss, a.area_id, a.SCHDUL_ID
|
||
|
|
, nvl((select ucm.fun_get_reg_acty(a.trgt_id) from dual ), '.') as ac_ty
|
||
|
|
from UCM.SACP_AREA_USE_HIST_PRCSNG a
|
||
|
|
, ucm.sacp_stats_times b
|
||
|
|
where dt >= in_ymd||in_hh||in_mi||in_ss
|
||
|
|
and dt <= out_ymd||out_hh||out_mi||out_ss
|
||
|
|
and a.area_ty = 'AF05003'
|
||
|
|
and a.insert_dt in (
|
||
|
|
select insert_dt
|
||
|
|
from ucm.SACP_AREA_USE_HIST_PRCSNG
|
||
|
|
where area_ty = 'AF05003'
|
||
|
|
and proc_se = 'S'
|
||
|
|
and in_ymd = '20240820'
|
||
|
|
and (in_hh = :ar_hh or out_hh = :ar_hh)
|
||
|
|
and schdul_id is not null )
|
||
|
|
) m
|
||
|
|
group by m.area_id, ac_ty, m.ymd, m.hh, m.SCHDUL_ID
|
||
|
|
order by SCHDUL_ID
|
||
|
|
|
||
|
|
|
||
|
|
```
|