
rem ********************************************************************
rem * Filename          : %M% - Version %I%
rem * Author            : Dave Cook
rem * Original          : 28-FEB-95
rem * Last Update       : %H% - %T%
rem * Description       : Shows concurrent request stat's for capacity
rem *                     planning purposes.
rem * Usage             : start cmpstats.sql <date>  e.g. 03-MAR-95
rem *                                        <Program Name>
rem *                                        <Excluded User>
rem *
rem ********************************************************************

def end_date=&&1
def u_prog_name=&&2
def person=&&3

def aps_prog    = 'cmpstats.sql-1'
def aps_title   = 'Concurrent Program Hourly Statistics for &end_date (! &person
)'
start apstitle

col program_name        format  a25     heading 'Program Name'  justify c trunc
col start_hour          format  a4      heading 'Hour'          justify c
col count               format  999     heading 'Count'         justify c
col avg_time            format  9990.00 heading 'Avg Time'      justify c
col min_time            format  9990.00 heading 'Min Time'      justify c
col max_time            format  9990.00 heading 'Max Time'      justify c
col avg_wtime           format  9990.00 heading 'Avg W Time'    justify c

select  a.user_concurrent_program_name program_name,
  to_char(c.request_date, 'hh24') start_hour,
  count(*) count,
  avg((c.actual_completion_date - c.request_date) * (24*60)) avg_time,
  min((c.actual_completion_date - c.request_date) * (24*60)) min_time,
  max((c.actual_completion_date - c.request_date) * (24*60)) max_time,
  avg((c.actual_start_date - c.request_date) * (24*60)) avg_wtime
from fnd_concurrent_programs a,
     fnd_concurrent_requests c
where c.concurrent_program_id = a.concurrent_program_id
  and a.user_concurrent_program_name like '&u_prog_name%'
  and c.status_code = 'C'
  and to_char(c.request_date, 'DD-MON-YY') = '&end_date'
  and c.requested_by not in (select user_id
                             from fnd_user
                             where user_name like '&person%'
                            )
group by a.user_concurrent_program_name,
         to_char(c.request_date, 'hh24')
order by 1;