create or replace view fj_itsm_task_stat_2111_stage as
select task_oid,
       nvl2(min(FLD_SYS_TEST_PASS_TIME), max(FLD_SYS_TEST_PASS_TIME), null) FLD_SYS_TEST_PASS_TIME, 
       nvl2(min(FLD_PLANNED_ON_LINE_TIME),max(FLD_PLANNED_ON_LINE_TIME), null) FLD_PLANNED_ON_LINE_TIME, 
       nvl2(min(fld_hope_finish_date), max(fld_hope_finish_date), null) fld_hope_finish_date,
       --nvl2(min(FLD_ON_LINE_TIME), max(FLD_ON_LINE_TIME), null) FLD_ON_LINE_TIME,
       nvl2((select min(j.online_date) from fj_online_job j,fj_online s where SUBSTR(j.reqid,0,INSTR(j.reqid,'-')) = SUBSTR(s.reqid,0,INSTR(s.reqid,'-')) and j.online_date is not null and s.task_oid = a.TASK_OID),
            (select max(j.online_date) from fj_online_job j,fj_online s where SUBSTR(j.reqid,0,INSTR(j.reqid,'-')) = SUBSTR(s.reqid,0,INSTR(s.reqid,'-')) and j.online_date is not null and s.task_oid = a.TASK_OID),
            null) FLD_ON_LINE_TIME, 
       nvl2((select min(fld_archive_time) from itsm_task_stat_2111 b where b.task_oid = a.task_oid),
            (select max(fld_archive_time) from itsm_task_stat_2111 b where b.task_oid = a.task_oid),
            null) fld_archive_time 
  from (select task_oid,
               FLD_STAGE_ID,
               FLD_PLANNED_ON_LINE_TIME,
               max(FLD_SYS_TEST_PASS_TIME) FLD_SYS_TEST_PASS_TIME, 
               max(fld_hope_finish_date) fld_hope_finish_date,
               max(fld_archive_time) fld_archive_time 
          from itsm_task_stat_2111_data
         group by task_oid, FLD_STAGE_ID, FLD_PLANNED_ON_LINE_TIME) a
 group by task_oid
;
