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