📄 collect.sql
字号:
CREATE PROCEDURE SP_RPT_COLLECT_DAY_TEST
(IN BEGINDATE VARCHAR(8),
IN ENDDATE VARCHAR(8),
IN WEEK VARCHAR(6)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
----*****************************************************************
----过 程 名：SP_RPT_COLLECT_day_TEST
----过程描述：
----运行系统：
----输入参数：
----输出参数：结果集, 错误代码
----依赖的过程和函数：
----依赖的视图和源表：
---- R_ROUTE_POIN_day
---- R_QU_BK_day
---- R_RP_BK_1001_day
---- R_QU_BK_1002_day
----编写人员：
----创建日期：2009-02
----*****************************************************************
P1: BEGIN
DECLARE sqlcode integer default 0;
declare v_notfound integer default 0;
declare tn_total_20s_95 float;
declare tn_total_20s_1010 float;
declare time_key95 varchar(25);
declare object_name95 varchar(8);
declare n_entered95 integer;
declare an_entered95 integer;
declare n_answered95 integer;
declare t_answered95 integer;
declare n_abandoned95 integer;
declare t_abandoned95 integer;
declare n_abandoned_5s95 integer;
declare t_abandoned_5s95 integer;
declare n_answered_20s95 integer;
declare n_abandoned_20s95 integer;
declare n_answered_timeout95 integer;
declare noc_answered_tot95 integer;
declare n_entered_62006_95 integer;
declare n_abandoned_62006_95 integer;
declare t_abandoned_62006_95 integer;
declare n_distributed_62006_95 integer;
declare t_distributed_62006_95 integer;
declare n_total_20s_95 float;
declare time_key10 varchar(25);
declare object_name10 varchar(8);
declare n_entered10 integer;
declare an_entered10 integer;
declare n_answered10 integer;
declare t_answered10 integer;
declare n_abandoned10 integer;
declare t_abandoned10 integer;
declare n_abandoned_5s10 integer;
declare t_abandoned_5s10 integer;
declare n_answered_20s10 integer;
declare n_abandoned_20s10 integer;
declare n_answered_timeout10 integer;
declare noc_answered_tot10 integer;
declare n_entered_62006_10 integer;
declare n_abandoned_62006_10 integer;
declare t_abandoned_62006_10 integer;
declare n_distributed_62006_10 integer;
declare t_distributed_62006_10 integer;
declare n_total_20s_10 float;
declare an_total_20s95 float;
declare an_total_20s10 float;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TB_RPT_MID_01
(
time_key varchar(25),
object_name varchar(8),
itotal_n_entered integer,
atotal_n_entered integer,
atotal_n_answered integer,
atotal_t_answered integer,
atotal_n_abandoned integer,
atotal_t_abandoned integer,
atotal_n_abandoned_5s integer,
atotal_t_abandoned_5s integer,
atotal_n_answered_20s integer,
atotal_n_abandoned_20s integer,
n_answered_timeout integer,
noc_answered_tot integer,
n_entered_62006 integer,
n_abandoned_62006 integer,
t_abandoned_62006 integer,
n_distributed_62006 integer,
t_distributed_62006 integer
)
NOT LOGGED
WITH REPLACE
;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TB_RPT_MID_11
(
time_key varchar(25),
object_name varchar(8),
itotal_n_entered integer,
atotal_n_entered integer,
atotal_n_answered integer,
atotal_t_answered integer,
atotal_n_abandoned integer,
atotal_t_abandoned integer,
atotal_n_abandoned_5s integer,
atotal_t_abandoned_5s integer,
atotal_n_answered_20s integer,
atotal_n_abandoned_20s integer,
n_answered_timeout integer,
noc_answered_tot integer,
n_entered_62006 integer,
n_abandoned_62006 integer,
t_abandoned_62006 integer,
n_distributed_62006 integer,
t_distributed_62006 integer
)
NOT LOGGED
WITH REPLACE
;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TB_RPT_MID_02
(
time_key varchar(255),
object_name varchar(8),
itotal_n_entered integer,
atotal_n_entered integer,
atotal_n_answered integer,
atotal_t_answered integer,
atotal_n_abandoned integer,
atotal_t_abandoned integer,
atotal_n_abandoned_5s integer,
atotal_t_abandoned_5s integer,
atotal_n_answered_20s integer,
atotal_n_abandoned_20s integer,
n_answered_timeout integer,
noc_answered_tot integer,
n_entered_62006 integer,
n_abandoned_62006 integer,
t_abandoned_62006 integer,
n_distributed_62006 integer,
t_distributed_62006 integer
)
NOT LOGGED
WITH REPLACE
;
P2: BEGIN
insert into SESSION.TB_RPT_MID_11
select t3.*,
t4.n_entered_62006,
t4.n_abandoned_62006,
t4.t_abandoned_62006,
t4.n_distributed_62006,
t4.t_distributed_62006
from (
select t1.time_key,
t1.object_name,
t1.itotal_n_entered,
t2.atotal_n_entered,
t2.atotal_n_answered,
t2.atotal_t_answered,
t2.atotal_n_abandoned,
t2.atotal_t_abandoned,
t2.atotal_n_abandoned_5s,
t2.atotal_t_abandoned_5s,
t2.atotal_n_answered_20s,
t2.atotal_n_abandoned_20s,
t2.n_answered_timeout,
t2.noc_answered_tot
from (
select time_key,object_name, sum(itotal_n_entered) as itotal_n_entered
from(
select time_key,
sum(n_entered) as itotal_n_entered,
object_id
from R_ROUTE_POIN_day
where object_id in ( select object_id
from O_ROUTE_POIN_day
where substr(object_name,1,5) in( '60001', '60003', '60005', '60007', '60009',
'60011', '60013', '60015', '60017', '60019',
'60021', '60023', '60025', '60027', '60029',
'60031', '60033', '60035', '60037', '60039',
'60041', '60043', '60047', '60049', '60051', '60055','60057','60059' ))
and time_key >= begindate and time_key <= enddate
group by time_key,object_id ) as t5
left join
(
select object_id,case when substr(object_name,1,5) ='60090' then '10105558' else '95558' end as object_name
from O_ROUTE_POIN_day
where substr(object_name,1,5) in( '60001', '60003', '60005', '60007', '60009',
'60011', '60013', '60015', '60017', '60019',
'60021', '60023', '60025', '60027', '60029',
'60031', '60033', '60035', '60037', '60039',
'60041', '60043', '60047', '60049', '60051', '60055','60057','60059')
) as t6
on t5.object_id =t6.object_id
group by time_key,object_name
) as t1
left join
(
select time_key,
sum(n_entered) as atotal_n_entered,
sum(n_answered) as atotal_n_answered,
sum(t_answered) as atotal_t_answered,
sum(n_abandoned) as atotal_n_abandoned,
sum(t_abandoned) as atotal_t_abandoned,
sum(abandoned_in_5s) as atotal_n_abandoned_5s,
sum(t_abandoned_5s) as atotal_t_abandoned_5s,
sum(answered_in_20s) as atotal_n_answered_20s,
sum(abandoned_in_20s) as atotal_n_abandoned_20s,
sum(n_answered_timeout) as n_answered_timeout,
sum(noc_answered_tot) as noc_answered_tot
from R_ROUTE_POIN_day
where object_id in ( select object_id
from O_ROUTE_POIN_day
where substr(object_name,1,5) in( '62001' ))
group by time_key
) as t2
on t1.time_key=t2.time_key
) as t3
left join
(
select time_key,
sum(n_enteredn) as n_entered_62006,
sum(n_abandoned) as n_abandoned_62006,
sum(t_abandoned) as t_abandoned_62006,
sum(n_distributed) as n_distributed_62006,
sum(t_distributed) as t_distributed_62006
from R_QU_BK_day
where object_id in ( select object_id
from O_QU_BK_day
where substr(object_name,1,5) in( '62006' ))
group by time_key
) as t4
on t3.time_key=t4.time_key
order by 1;
insert into SESSION.TB_RPT_MID_02
select t10.*,
t11.n_entered_62006,
t11.n_abandoned_62006,
t11.t_abandoned_62006,
t11.n_distributed_62006,
t11.t_distributed_62006
from (
select t8.time_key,
t8.object_name,
t8.itotal_n_entered,
t9.atotal_n_entered,
t9.atotal_n_answered,
t9.atotal_t_answered,
t9.atotal_n_abandoned,
t9.atotal_t_abandoned,
t9.atotal_n_abandoned_5s,
t9.atotal_t_abandoned_5s,
t9.atotal_n_answered_20s,
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -