@@header

/*
Job State
Not Started(0)
Running (1)
Finished(2)
*/

COLUMN job_state           FORMAT a15
COLUMN  "Count"            FORMAT 99999
COLUMN  max_start_date     FORMAT a18

SELECT * FROM 
(
select * from 
(
select wl.program_name
     , count(1)  "Count"
     , DECODE(wl.job_state
              ,'-1','Error(-1)'
              ,'0','Not Started(0)'
              ,'1','Running (1)'
              ,'2','Finished(2)'
              ,wl.job_state
              ) job_state
     , TO_CHAR(max(wl.start_date),'DD-MON-YY HH24:MI:SS') max_start_date
     , TO_CHAR(max(wl.end_date),'DD-MON-YY HH24:MI:SS') max_end_date
     , TO_CHAR(min(wl.start_date),'DD-MON-YY HH24:MI:SS') min_start_date
     , TO_CHAR(min(wl.end_date),'DD-MON-YY HH24:MI:SS') min_end_date
from pm_own.pm_work_list wl
where 1=1 
--and program_name ='ctrf'
and wl.program_name in ( select distinct program_name from pm_own.pm_work_list where job_state = 1)
and wl.start_date > sysdate - 1
group by wl.program_name, DECODE(wl.job_state
                              ,'-1','Error(-1)'
                              ,'0','Not Started(0)'
                              ,'1','Running (1)'
                              ,'2','Finished(2)'
                              ,wl.job_state
                              )
order by  max(wl.start_date) desc
)
where rownum <= 20
)
ORDER BY program_name, job_state
;

@@footer