Obsidian/Recognition/Work Related/1.업무메모/2024-04-25.md

-- 일자별
select zone_id
    , (select UCM.FUN_GET_CMMN_NM('CT003', zone_id) from dual) as zone_nm
    , pad_no
	, sum(decode(substr(flt_dt,7,2), '01', 1, 0)) as day01
	, sum(decode(substr(flt_dt,7,2), '02', 1, 0)) as day02
	, sum(decode(substr(flt_dt,7,2), '03', 1, 0)) as day03
	, sum(decode(substr(flt_dt,7,2), '04', 1, 0)) as day04
	, sum(decode(substr(flt_dt,7,2), '05', 1, 0)) as day05
	, sum(decode(substr(flt_dt,7,2), '06', 1, 0)) as day06
	, sum(decode(substr(flt_dt,7,2), '07', 1, 0)) as day07
	, sum(decode(substr(flt_dt,7,2), '08', 1, 0)) as day08
	, sum(decode(substr(flt_dt,7,2), '09', 1, 0)) as day09
	, sum(decode(substr(flt_dt,7,2), '10', 1, 0)) as day10
	, sum(decode(substr(flt_dt,7,2), '11', 1, 0)) as day11
	, sum(decode(substr(flt_dt,7,2), '12', 1, 0)) as day12
	, sum(decode(substr(flt_dt,7,2), '13', 1, 0)) as day13
	, sum(decode(substr(flt_dt,7,2), '14', 1, 0)) as day14
	, sum(decode(substr(flt_dt,7,2), '15', 1, 0)) as day15
	, sum(decode(substr(flt_dt,7,2), '16', 1, 0)) as day16
	, sum(decode(substr(flt_dt,7,2), '17', 1, 0)) as day17
	, sum(decode(substr(flt_dt,7,2), '18', 1, 0)) as day18
	, sum(decode(substr(flt_dt,7,2), '19', 1, 0)) as day19
	, sum(decode(substr(flt_dt,7,2), '20', 1, 0)) as day20
	, sum(decode(substr(flt_dt,7,2), '21', 1, 0)) as day21
	, sum(decode(substr(flt_dt,7,2), '22', 1, 0)) as day22
	, sum(decode(substr(flt_dt,7,2), '23', 1, 0)) as day23
	, sum(decode(substr(flt_dt,7,2), '24', 1, 0)) as day24
	, sum(decode(substr(flt_dt,7,2), '25', 1, 0)) as day25
	, sum(decode(substr(flt_dt,7,2), '26', 1, 0)) as day26
	, sum(decode(substr(flt_dt,7,2), '27', 1, 0)) as day27
	, sum(decode(substr(flt_dt,7,2), '28', 1, 0)) as day28
	, sum(decode(substr(flt_dt,7,2), '29', 1, 0)) as day29
	, sum(decode(substr(flt_dt,7,2), '30', 1, 0)) as day30
	, sum(decode(substr(flt_dt,7,2), '31', 1, 0)) as day31
from (
	 select FLT_DE
		, SCHDUL_ID
		, CLSGN
		, AC_REG_NO
		, PAD_NO
		, DEICING_NO
		, IF_DT
		, IF_SRC
		, AC_TY_IATA
		, AC_TY_ICAO
		, AC_GRAD
		, EXP_DEP_DT
		, DEICING_REQ_DT
		, EXP_PAD_IN_DT
		, EXP_DEICING_START_DT
		, EXP_DEICING_END_DT
		, EXP_PAD_OUT_DT
		, ACTL_PAD_IN_DT
		, ACTL_DEICING_START_DT
		, nvl(ACTL_DEICING_END_DT, EXP_DEICING_END_DT) as ACTL_DEICING_END_DT
		, nvl(ACTL_PAD_OUT_DT, EXP_PAD_OUT_DT) as ACTL_PAD_OUT_DT
		, DEICING_ZONE_CD
		, EXP_DEICING_TIME
		, INTRFC_DT_KST
		, (select ucm.fun_zone_id(PAD_NO) from dual) as zone_id
	from uic.V_FLT_DEICING) a
where a 
and actl_pad_in_dt is not null
and ( substr(ACTL_PAD_IN_DT,1,6) = :ar_month or substr(ACTL_PAD_IN_DT,1,6) = :ar_month )
group by zone_id, pad_no
order by zone_id, pad_no