find the number of jobs requested for a queue by each hour in one day useful to find out if the nbr of workers is sufficient for the requests

/* cmqhourly.sql
        find the number of jobs requested for a queue by each hour in one day
useful to find out if the nbr of workers is sufficient for the requests
SDR-OCS 10/16/02 Creation based on archlogs.sql and cmqjobs.sql
*/
set verify off
set lines 160
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
col ccpid head ProgID-Name format a15 trunc
break on report
compute sum of 11PM on report
compute sum of 10PM on report
compute sum of 9PM on report
compute sum of 8PM on report
compute sum of 7PM on report
compute sum of 6PM on report
compute sum of 5PM on report
compute sum of 4PM on report
compute sum of 3PM on report
compute sum of 2PM on report
compute sum of 1PM on report
compute sum of Noon on report
compute sum of 11AM on report
compute sum of 10AM on report
compute sum of 9AM on report
compute sum of 8AM on report
compute sum of 7AM on report
compute sum of 6AM on report
compute sum of 5AM on report
compute sum of 4AM on report
compute sum of 3AM on report
compute sum of 2AM on report
compute sum of 1AM on report
compute sum of MidN on report
accept trgtqname char default NONE prompt 'Limit to which ccmgr queue name : '
accept trgtprog char default ALL prompt 'Limit to which ccmgr job name : '
accept trgtdte char default TODAY prompt 'Limit to what date (dd-mon-yyyy) : '
accept trgtproc char default ALL prompt 'Limit to which ccmgr os pid : '
select g.concurrent_program_id || '-' || g.concurrent_program_name ccpid,
sum(decode(to_char(requested_start_date,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(requested_start_date,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(requested_start_date,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(requested_start_date,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(requested_start_date,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(requested_start_date,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(requested_start_date,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(requested_start_date,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(requested_start_date,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(requested_start_date,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(requested_start_date,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(requested_start_date,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(requested_start_date,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(requested_start_date,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(requested_start_date,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(requested_start_date,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(requested_start_date,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(requested_start_date,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(requested_start_date,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(requested_start_date,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(requested_start_date,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(requested_start_date,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(requested_start_date,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(requested_start_date,'hh24'),'23',1,0)) "11PM"
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       applsys.fnd_concurrent_queues q
 where r.controlling_manager (+) = p.concurrent_process_id
   and p.queue_application_id = q.application_id
   and p.concurrent_queue_id = q.concurrent_queue_id
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and trunc(r.requested_start_date) = decode(upper('&trgtdte'),'TODAY',trunc(sysdate),'&trgtdte')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
   and (p.os_process_id = '&trgtproc' or upper('&trgtproc') = 'ALL')
group by g.concurrent_program_id || '-' || g.concurrent_program_name
order by 1
/          

prompt Getting queue statistics.....
set verify off
set lines 132
prompt Note - times are expressed in decimal minutes
col qname head "Activated Concurrent Queue" format a26
col actual head "Actual" format 999999
col target head "Target" format 999999
col minr head MinRTime format 9999.99
col maxr head MaxRTime format 9999.99
col avgr head AvgRun format 9999.99
col avgq head AvgQTime format 9999.99
col totreq head "# Jobs" format 99999
col qname head "Queue Name" format a25 trunc
clear breaks
clear computes
select  q.concurrent_queue_name || '(' || q.target_node || ')' qname,
count(r.request_id) totreq,
       q.running_processes actual,
       q.max_processes target,
       avg((r.actual_start_date - r.requested_start_date)*1440)   avgq,
       min((r.actual_completion_date - r.actual_start_date)*1440) minr,
       max((r.actual_completion_date - r.actual_start_date)*1440) maxr,
       avg((r.actual_completion_date - r.actual_start_date)*1440) avgr
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       applsys.fnd_concurrent_queues q
 where r.controlling_manager = p.concurrent_process_id
   and p.queue_application_id = q.application_id
   and p.concurrent_queue_id = q.concurrent_queue_id
   and trunc(r.requested_start_date) = decode(upper('&trgtdte'),'TODAY',trunc(sysdate),'&trgtdte')
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
group by q.concurrent_queue_name || '(' || q.target_node || ')',
       q.running_processes, q.max_processes;