[Casimiro Notevi]

Lo he humanizado un poco

Por cierto, te aconseja que "dividas y vencerás", porque eso así, a lo bruto, es inmasticable.

Código SQL [-]
CREATE OR ALTER PROCEDURE SP_AGN_PERF ( pdatefrom varchar(8), pdateto varchar(8), 
                                        ptimefrom varchar(6), ptimeto varchar(6), psectorid integer) 
  returns ( agentfullname varchar(30), agentid varchar(10), skill smallint, i_received smallint, i_answered smallint, 
            i_short smallint, i_returned smallint, i_talktime char(10), logintime char(10), idletime char(10), 
            oreceived smallint, oanswered smallint, oshort smallint, oreturned smallint, otalktime char(10), userid integer) 
as 
  declare variable script1 varchar(5000); 
  declare variable tmpagentid varchar(10); 
  declare variable lwhere varchar(1024); 
  declare variable tmplogin integer; 
  declare variable tmpidle integer; 
  declare variable tmp_i_talk integer; 
  declare variable tmp_o_talk integer; 
  declare variable scrpt varchar(100); 
begin -- Inicializa las vari 
  agentfullname = ''; 
  agentid = ''; 
  skill = 0; 
  i_received = 0; 
  i_answered = 0; 
  i_short = 0; 
  i_returned = 0; 
  i_talktime = '00:00:00'; 
  logintime = '00:00:00'; 
  idletime = '00:00:00'; 
  o_received = 0; 
  o_answered = 0; 
  o_short = 0; 
  o_returned = 0; 
  o_talktime = '00:00:00'; 
  userid = 0; 
  LWHERE = 'logdate between '''|| pdatefrom ||''' and '''||pdateto ||''' ' || 'and logtime between ' ||
            ptimefrom||' and '||ptimeto; if ((P_SECTORID is not NULL) and (P_SECTORID > 0)) then LWHERE = :LWHERE ||
            ' and sectorid = '||:P_SECTORID; 
  script1 = 'select userid, agentid, skill, ' || 'Sum(Case when calltype = ''I'' then 1 else 0 end), ' || 
            'Sum(Case when calltype = ''I'' and talktime > 0 then 1 else 0 end), ' || 'Sum(Case when calltype = ''I'' 
            and talktime between 1 and 20 then 1 else 0 end), ' || 'Sum(Case when calltype = ''I'' and talktime = 0 
            and delaytime > 0 then 1 else 0 end), ' || 'Sum(Case when calltype = ''I'' then talktime + holdtime + 
            cnsholdtime else 0 end), ' || 'Sum(Case when calltype = ''O'' then 1 else 0 end), ' || 'Sum(Case when calltype = ''O'' 
            and talktime > 0 then 1 else 0 end), ' || 'Sum(Case when calltype = ''O'' and talktime between 1 and 20 then 1 
            else 0 end), ' || 'Sum(Case when calltype = ''O'' and talktime = 0 and delaytime > 0 then 1 else 0 end), 
            ' || 'Sum(Case when calltype = ''O'' then talktime + holdtime + cnsholdtime else 0 end) ' || 'from logpos ' || '
            where '||LWHERE|| ' group by userid, agentid, skill order by agentid, skill'; 
   for execute statement script1 into :userid, :agentid, :skill, :i_received, :i_answered, :i_short, :i_returned, 
              :tmp_i_talk, oreceived, oanswered, oshort, oreturned, :tmp_o_talk do begin select sum(interval_t), sum(idletime) 
   from logposint 
   where userid = :userid 
   and (logdate between pdatefrom and pdateto) 
   and (logtime between ptimefrom and ptimeto) 
   into :tmplogin, :tmpidle; 
   select * from sp_secsto_hhmmss(:tmplogin) into logintime; 
   select * from sp_secsto_hhmmss(:tmpidle) into idletime; 
   select * from sp_secsto_hhmmss(:tmp_i_talk) into i_talktime; 
   select * from sp_secsto_hhmmss(:tmp_o_talk) into o_talktime; 
   execute statement 'select user_fullname from users where userid = :userid' on external data source '192.168.x.x:sched' 
                      as user 'SYSDBA' password 'masterkey' 
     into :agentfullname; 
     suspend; 
   end 
 end