3,491
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure Log0001_TotalCallIn
(
P_StartTime varchar2,
p_StopTime varchar2,
v_cur out PackageSYS_TotalCllIn.cur_SYS_TotalCllIn,
p_TimeType int
)
is
v_sql varchar2(4000) :='';
begin
v_sql := 'Select A.StartTime as 时间分组,nvl(A.Counts,0) as 呼入总次数,nvl(B.Counts,0) as 成功总次数,nvl(C.Counts,0) as 失败总次数,';
v_sql := v_sql || ' nvl(D.Counts,0) as 转人工总次数,nvl(E.Counts,0) as 转人工成功次数,';
v_sql := v_sql || ' (case when nvl(D.Counts,0)=0 then 0 else nvl(E.Counts,0)*100/D.Counts end) as 转人工接通率,(nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数 from';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(*) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and ServiceChainIndex = 0 group by to_char(StartTime,''yyyy-mm-dd hh24'') ) A left outer join ';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(*) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and (ServiceChainIndex = 1) AND (EndReason = ''11'') AND (ISO <> ''T'')';
v_sql := v_sql || ' group by to_char(StartTime,''yyyy-mm-dd hh24'') ) B on A.StartTime=B.StartTime left outer join';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(*) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and ((ServiceChainIndex = 0) And (EndReason<>''7'') And (EndReason<>''515'')) OR ((ServiceChainIndex = 1) AND (EndReason <> ''11'') AND';
v_sql := v_sql || ' (ISO <> ''T'') And (SPType is null)) group by to_char(StartTime,''yyyy-mm-dd hh24'') ) C on A.StartTime=C.StartTime left outer join';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(distinct SRID) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and ServiceChainIndex = 0 and SRID in (Select distinct SRID from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and (EndReason = 21 or EndReason = 22 or EndReason = 23 or EndReason = 24 or EndReason = 25';
v_sql := v_sql || ' or EndReason = 26 or EndReason = 27 or EndReason = 28 or EndReason = 29) and ISO<>''T'')';
v_sql := v_sql || ' group by to_char(StartTime,''yyyy-mm-dd hh24'') ) D on A.StartTime=D.StartTime left outer join';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(distinct SRID) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and ServiceChainIndex = 0 and SRID in (Select distinct SRID from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and (EndReason = 21 ) and ISO<>''T'') group by to_char(StartTime,''yyyy-mm-dd hh24'')';
v_sql := v_sql || ' ) E on A.StartTime=E.StartTime order by A.StartTime ';
DBMS_OUTPUT.put_line(v_sql);
open v_cur for v_sql;
end Log0001_TotalCallIn;