CREATE OR REPLACE PROCEDURE ZHYW.SHZC_YYT_MRSL_IPYW_GC
(p_day varchar2 default to_char(add_months(sysdate-1,0),'yyyymmdd') ) AS
--- p_day 获取日期，默认为昨天 yyyymmdd ########## ipaddress 做排序
SQLSTMT0 VARCHAR2(32760);
SQLSTMT1 VARCHAR2(32760);
V_ZMLX VARCHAR2(20);
V_ZMLX_SZ VARCHAR2(20);
v_mon varchar2(10);
gd_state varchar2(20);
BEGIN
v_mon:=substr(p_day,1,6); ---月
--自办厅
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqd'),'SHZC');
SQLSTMT0 := 'create table SHZC.wq_khsl_rsj_zbqd as
select a.片区归属区县,a.orgid,a.orgname,a.unit_id,a.unit_name,a.是否自办渠道,a.单位类型,a.渠道类型,a.渠道类别
from zhyw.shc_organization a where a.status=1
and a.是否自办营业厅 is not null ';
EXECUTE IMMEDIATE (SQLSTMT0);
---2022-0620 客服要求剔除后台工号 shzc.wq_khsl_rsj_zbqd_htgh
----客服要求剔除后台工号
---and a.recopid not in (select t.operid from shzc.wq_khsl_rsj_zbqd_htgh t )
--受理记录
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqda'),'SHZC');
SQLSTMT0 := 'create table SHZC.wq_khsl_rsj_zbqda as
select a.oid,a.formnum,a.entitytype,a.entityid,a.custid,a.contacttype,a.servnumber,a.recdefid,
a.recorgid,a.recopid,a.recdate,a.isbackprocess,a.isrollback,a.macaddr,
--nvl(case when nvl(replace(a.ipaddress,''.'',''#''),''0'') not like ''%#%'' then a.recopid end,ipaddress)
ipaddress,
a.recfee,
a.discount,a.notes,a.status,a.statusdate,a.subrecdefid,a.delegateidtype,a.delegateid
from zhyw.shc_reception a,
SHZC.wq_khsl_rsj_zbqd b
where a.recorgid=b.orgid
and length(a.servnumber)=11
and substr(a.servnumber,1,1)=''1''
and ISBACKPROCESS=''0''
and nvl(a.servnumber,''0'') <>''13000000000''
and to_char(a.recdate,''yyyymmdd'')='''||p_day||'''
----如果找不到ip的就不要
and nvl(replace(a.ipaddress,''.'',''#''),''0'') like ''%#%'' ';
EXECUTE IMMEDIATE (SQLSTMT0);
---------2022-0711--剔除单台席业务量20笔以内的数据
SQLSTMT0 := 'delete shzc.wq_khsl_rsj_zbqda_ipcs a where cycle='''||p_day||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
SQLSTMT0 := 'insert into SHZC.wq_khsl_rsj_zbqda_ipcs
select to_char(a.recdate,''yyyymmdd'') cycle,a.ipaddress,count(distinct a.oid) oid_cs
from SHZC.wq_khsl_rsj_zbqda a
group by to_char(a.recdate,''yyyymmdd''),a.ipaddress ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
----2022--0623 主体业务情况
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqda_cpyw'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqda_cpyw as
select a.subrecdefid,count(*) ss
from SHZC.wq_khsl_rsj_zbqda a
where a.subrecdefid is not null
group by a.subrecdefid ';
EXECUTE IMMEDIATE (SQLSTMT0);
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqda_cpywa'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqda_cpywa as
select a.dictid,a.dictname,a.groupid,a.status from
(select a.*,row_number() over (partition by a.dictid order by a.SORTORDER,a.STATUSDATE desc ) 排名
from tbcs.dict_item@bcv a,
shzc.wq_khsl_rsj_zbqda_cpyw b
where a.dictid=b.subrecdefid
and a.status=1) a
where 排名=1 ';
EXECUTE IMMEDIATE (SQLSTMT0);
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqda_cpyw_bd
select * from shzc.wq_khsl_rsj_zbqda_cpywa b
where not exists (select 1 from shzc.wq_khsl_rsj_zbqda_cpyw_bd t where t.dictid=b.dictid) ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqda_cpywb'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqda_cpywb as
select a.oid,a.servnumber,a.recdate,a.ipaddress,a.contacttype,c.dictname,a.recdefid,b.itemname,a.subrecdefid,d.dictname subrecdefname
from SHZC.wq_khsl_rsj_zbqda a,
tbcs.reception_define@bcv b,
(select * from tbcs.dict_item@bcv where groupid=''bsac'') c,
shzc.wq_khsl_rsj_zbqda_cpyw_bd d
where a.recdefid=b.itemid
and a.contacttype =c.dictid(+)
and a.subrecdefid=d.dictid(+) ';
EXECUTE IMMEDIATE (SQLSTMT0);
--涉及号码取 subsid
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdb'),'SHZC');
SQLSTMT0 := 'create table SHZC.wq_khsl_rsj_zbqdb as
select * from
(select a.servnumber,a.subsid,a.acctid,a.prodid,a.STATUS,a.statusdate 状态时间,
a.createdate 入网时间,
row_number() over (partition by a.servnumber order by nvl(a.createdate,sysdate-9999) desc ) 排名
from zhyw.subscriber a ,
(select distinct b.servnumber from SHZC.wq_khsl_rsj_zbqda b ) b
where a.servnumber=b.servnumber
and a.status not in (''US26'',''US28''))a
where a.排名=1 ';
EXECUTE IMMEDIATE (SQLSTMT0);
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdc'),'SHZC');
SQLSTMT0 := 'create table SHZC.wq_khsl_rsj_zbqdc as
select a.*,b.subsid,b.status status_user,d.HD_IDEN_NO,d.HD_CUST_NAME
from SHZC.wq_khsl_rsj_zbqda a,
SHZC.wq_khsl_rsj_zbqdb b,
shzc.shzc_age_jqb d
where a.servnumber=b.servnumber(+)
and b.subsid=d.subsid(+) ';
EXECUTE IMMEDIATE (SQLSTMT0);
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdd'),'SHZC');
SQLSTMT0 := 'create table SHZC.wq_khsl_rsj_zbqdd as
select * from
(select a.*,row_number() over (partition by a.subsid order by STARTDATE desc ) 排名
from tbcs.GROUP_SUBS_MEMBER@bcv a,
(select distinct b.subsid from SHZC.wq_khsl_rsj_zbqdc b where b.subsid is not null) b
where a.subsid=b.subsid
and upper(PRODID) like upper(''%family%'')
and nvl(a.enddate,sysdate+9999)>sysdate-2) a
where 排名=1 ';
EXECUTE IMMEDIATE (SQLSTMT0);
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqde'),'SHZC');
SQLSTMT0 := 'create table SHZC.wq_khsl_rsj_zbqde as
select a.*,b.groupoid,row_number() over (partition by a.ipaddress order by a.recdate ,a.oid ) 排名
from SHZC.wq_khsl_rsj_zbqdc a,
SHZC.wq_khsl_rsj_zbqdd b
where a.subsid=b.subsid(+)
order by a.oid ';
EXECUTE IMMEDIATE (SQLSTMT0);
---用户办理的优惠
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdf_yh'),'SHZC');
SQLSTMT0 := 'create table SHZC.wq_khsl_rsj_zbqdf_yh as
select h.oid,b.prodid,pp.prodname,b.privid,c.privname,
b.startdate,b.enddate,b.applyoperid,b.APPLYOID,
b.CANCELOID,b.CANCELOPERID,''开通'' type_state
from SHZC.wq_khsl_rsj_zbqde h,
zhyw.subs_privilege b,
TBCS.privilege_SCHEME@BCV c,
tbcs.product@bcv pp
where h.oid=b.applyoid(+)
and b.privid=c.privid(+)
and b.prodid=pp.prodid(+)
union all
select h.oid,b.prodid,pp.prodname,b.privid,c.privname,
b.startdate,b.enddate,b.applyoperid,b.APPLYOID,
b.CANCELOID,b.CANCELOPERID,''取消'' type_state
from SHZC.wq_khsl_rsj_zbqde h,
zhyw.subs_privilege b,
TBCS.privilege_SCHEME@BCV c,
tbcs.product@bcv pp
where h.oid=b.canceloid(+)
and b.privid=c.privid(+)
and b.prodid=pp.prodid(+) ';
EXECUTE IMMEDIATE (SQLSTMT0);
---循环开始清除本地表里当天受理，从新开始
SQLSTMT0 := 'delete shzc.wq_khsl_rsj_zbqdf_bd a ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
DECLARE CURSOR ZMLX IS select a.oid from SHZC.wq_khsl_rsj_zbqde a order by a.oid;
BEGIN OPEN ZMLX;
--因为是插入，这个数据用不上
--SQLSTMT0 := 'create table SHZC.SHZC_JFBB_QXMC_'|| ZDYF ||' storage (initial 8m next 3m pctincrease 0) as';
LOOP FETCH ZMLX INTO V_ZMLX; --循环开始
EXIT WHEN ZMLX%NOTFOUND;
select nvl((select t.servnumber from shzc.wq_khsl_rsj_zbqdf_bd t
where exists (
select 1 from shzc.wq_khsl_rsj_zbqde a,
shzc.wq_khsl_rsj_zbqde b
where a.ipaddress=b.ipaddress(+)
and a.servnumber=b.servnumber(+)
and a.排名=b.排名(+)-1
and a.oid=V_ZMLX
and t.servnumber=a.servnumber
and t.h_recdate=a.recdate)
and rownum=1
),'0') into gd_state from dual;
if gd_state <>'0' then ----如果找到记录就修改失效时间为
SQLSTMT0 := 'update shzc.wq_khsl_rsj_zbqdf_bd t set t.h_recdate=(select nvl(b.recdate,a.recdate)
from shzc.wq_khsl_rsj_zbqde a,
shzc.wq_khsl_rsj_zbqde b
where a.ipaddress=b.ipaddress(+)
and a.servnumber=b.servnumber(+)
and a.排名=b.排名(+)-1
and a.oid= '''||V_ZMLX ||'''
and t.servnumber=a.servnumber
and t.h_recdate=a.recdate )
where (t.servnumber,t.h_recdate) in (
select a.servnumber,a.recdate
from shzc.wq_khsl_rsj_zbqde a,
shzc.wq_khsl_rsj_zbqde b
where a.ipaddress=b.ipaddress(+)
and a.servnumber=b.servnumber(+)
and a.排名=b.排名(+)-1
and a.oid= '''||V_ZMLX ||''') ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
else ----如果没有找到记录就插入一条记录
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqdf_bd
select a.recorgid,a.recopid,a.oid,a.formnum,a.servnumber,a.subsid,a.groupoid,
a.HD_IDEN_NO,a.HD_CUST_NAME,a.recdate,
a.contacttype,a.recdefid,a.SUBRECDEFID,nvl(b.recdate,a.recdate) h_recdate,a.ipaddress
from shzc.wq_khsl_rsj_zbqde a,
shzc.wq_khsl_rsj_zbqde b
where a.ipaddress=b.ipaddress(+)
and a.servnumber=b.servnumber(+)
and a.排名=b.排名(+)-1
and a.oid = '''||V_ZMLX ||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
end if;
END LOOP; --循环结束
--SQLSTMT0 := SQLSTMT0 || ' ';
--语句截取组成完整语句
--SQLSTMT1 := SUBSTR(SQLSTMT0 , 1 , length( SQLSTMT0 )-11) ;
--EXECUTE IMMEDIATE (SQLSTMT1);
end;
----按受理工号排序，提取前一笔业务的结束时间，如果前面没有业务就用自己的填充
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdf'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdf as
select a.*,row_number() over (partition by a.ipaddress order by a.recdate ,a.oid ) 排名
from shzc.wq_khsl_rsj_zbqdf_bd a
order by a.oid ';
EXECUTE IMMEDIATE (SQLSTMT0);
---按照工号排序后补充前一笔业务的最后受理时间
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdfa'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdfa as
select a.recorgid,
a.recopid,
a.oid,
a.formnum,
a.servnumber,
a.subsid,
a.groupoid,
a.hd_iden_no,
a.hd_cust_name,
a.contacttype,
a.recdefid,
a.subrecdefid,
nvl(b.h_recdate,a.recdate) q_recdate,
a.recdate,
a.h_recdate,
a.ipaddress
from shzc.wq_khsl_rsj_zbqdf a,
shzc.wq_khsl_rsj_zbqdf b
where a.ipaddress=b.ipaddress(+)
and a.排名=b.排名(+)+1 ';
EXECUTE IMMEDIATE (SQLSTMT0);
------------2022-0620 客服要求
------------1.原始数据：对“单业务超30分钟、等候时长超30分钟（当前客户业务办理结束时间-上一客户业务办理结束时间）”在表格后面单独做标记；
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqd_yssja'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqd_yssja as
select a.recorgid,t.orgname,
a.recopid,
a.ipaddress,
a.oid,
a.formnum,
a.servnumber,
a.subsid,
a.groupoid,
a.hd_iden_no,
a.hd_cust_name,
a.contacttype,c.dictname,
a.recdefid,b.itemname,
a.subrecdefid,
a.q_recdate,
a.recdate,
a.h_recdate,
round((a.recdate-a.q_recdate)*24*60,1) 等候时长 ,
round((a.h_recdate-a.recdate)*24*60,1) 单业务时长
from shzc.wq_khsl_rsj_zbqdfa a,
tbcs.organization@bcv t,
tbcs.reception_define@bcv b,
(select * from tbcs.dict_item@bcv where groupid=''bsac'') c
where a.recorgid=t.orgid(+)
and a.recdefid=b.itemid
and a.contacttype =c.dictid(+)
and to_char(a.recdate,''yyyymmdd'')='''||p_day||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqd_yssjb'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqd_yssjb as
select a.*,
--如果前一笔业务12点前，受理是在12点后，减去12点内时间
round(nvl(case when to_char(a.q_recdate,''hh24mi'')<=''1200'' and to_char(a.recdate,''hh24mi'')>=''1330''
then ((a.recdate-to_date(to_char(a.recdate,''yyyymmdd'')||''1200'',''yyyymmddhh24mi'')) - (a.recdate-to_date(to_char(a.recdate,''yyyymmdd'')||''1330'',''yyyymmddhh24mi'')))*24*60
when to_char(a.q_recdate,''hh24mi'')<=''1200'' and to_char(a.recdate,''hh24mi'')>=''1200'' and to_char(a.recdate,''hh24mi'')<=''1330''
then ((a.recdate-to_date(to_char(a.recdate,''yyyymmdd'')||''1200'',''yyyymmddhh24mi'')) )*24*60
when to_char(a.q_recdate,''hh24mi'')>=''1200'' and to_char(a.q_recdate,''hh24mi'')<=''1330'' and to_char(a.recdate,''hh24mi'')>=''1330''
then ((to_date(to_char(a.recdate,''yyyymmdd'')||''1330'',''yyyymmddhh24mi'')-a.q_recdate) )*24*60 end,0),1) 等候时长休息,
--如果业务12点前，最后受理是在12点后，减去12点内时间
round(nvl(case when to_char(a.recdate,''hh24mi'')<=''1200'' and to_char(a.h_recdate,''hh24mi'')>=''1330''
then ((a.h_recdate-to_date(to_char(a.recdate,''yyyymmdd'')||''1200'',''yyyymmddhh24mi'')) - (a.h_recdate-to_date(to_char(a.recdate,''yyyymmdd'')||''1330'',''yyyymmddhh24mi'')))*24*60
when to_char(a.recdate,''hh24mi'')<=''1200'' and to_char(a.h_recdate,''hh24mi'')>=''1200'' and to_char(a.h_recdate,''hh24mi'')<=''1330''
then ((a.h_recdate-to_date(to_char(a.recdate,''yyyymmdd'')||''1200'',''yyyymmddhh24mi'')) )*24*60
when to_char(a.recdate,''hh24mi'')>=''1200'' and to_char(a.recdate,''hh24mi'')<=''1330'' and to_char(a.h_recdate,''hh24mi'')>=''1330''
then (to_date(to_char(a.recdate,''yyyymmdd'')||''1330'',''yyyymmddhh24mi'')-a.recdate )*24*60 end,0),1) 单业务时长休息
from shzc.wq_khsl_rsj_zbqd_yssja a ';
EXECUTE IMMEDIATE (SQLSTMT0);
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqd_yssjc'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqd_yssjc as
select a.*,nvl(case
when a.等候时长-a.等候时长休息>=30 and a.单业务时长-a.单业务时长休息>=30 then ''等候与办理时长''
when a.等候时长-a.等候时长休息>=30 then ''等候时长''
when a.单业务时长-a.单业务时长休息>=30 then ''单业务时长'' end ,''正常'') 是否超长
from shzc.wq_khsl_rsj_zbqd_yssjb a ';
EXECUTE IMMEDIATE (SQLSTMT0);
SQLSTMT0 := 'delete shzc.wq_khsl_rsj_zbqd_yssj_bd a where to_char(a.recdate,''yyyymmdd'')='''||p_day||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
---------2022--0620-原始数据 结果存档
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqd_yssj_bd
select * from shzc.wq_khsl_rsj_zbqd_yssjc ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
---------------按受理工号和号码排序，看是否1小时内
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdg'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdg as
select a.*,row_number() over (partition by a.ipaddress,a.servnumber order by a.recdate ,a.oid ) 排名
from shzc.wq_khsl_rsj_zbqdfa a
---2022-0620-客服要求剔除后台工号
---2022-0630-客服又不要剔除后台工号了
---where a.recopid not in (select t.operid from shzc.wq_khsl_rsj_zbqd_htgh t )
order by a.oid ';
EXECUTE IMMEDIATE (SQLSTMT0);
---同号码同工号如果第一笔开始受理时间与第二笔最后受理时间小于1小时，认定为连续的，这中间的业务不计算
---循环开始清除本地表里当天受理，从新开始
SQLSTMT0 := 'delete shzc.wq_khsl_rsj_zbqdg_bd a ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
DECLARE CURSOR ZMLX IS select a.oid from SHZC.wq_khsl_rsj_zbqdg a order by a.oid ;
BEGIN OPEN ZMLX;
--因为是插入，这个数据用不上
--SQLSTMT0 := 'create table SHZC.SHZC_JFBB_QXMC_'|| ZDYF ||' storage (initial 8m next 3m pctincrease 0) as';
LOOP FETCH ZMLX INTO V_ZMLX; --循环开始
EXIT WHEN ZMLX%NOTFOUND;
--1小时以内的改结束时间
select nvl((select a.servnumber from shzc.wq_khsl_rsj_zbqdg a,
shzc.wq_khsl_rsj_zbqdg b
where a.recopid=b.recopid
and a.servnumber=b.servnumber
and a.排名=b.排名-1
and rownum=1
and a.recdate >= nvl(b.h_recdate,sysdate+999)-(1/48)
and a.oid=V_ZMLX ),'0') into gd_state
from dual;
if gd_state <>'0' then ----如果有对应记录就插入后一条结束时间
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqdg_bd
select a.recorgid,a.recopid,a.oid,a.formnum,a.servnumber,a.subsid,a.groupoid,
a.HD_IDEN_NO,a.HD_CUST_NAME,a.contacttype,a.recdefid,a.SUBRECDEFID,
a.q_recdate,a.recdate,b.h_recdate,a.ipaddress
from shzc.wq_khsl_rsj_zbqdg a,
shzc.wq_khsl_rsj_zbqdg b
where a.recopid=b.recopid
and a.servnumber=b.servnumber
and a.排名=b.排名-1
and a.recdate >= nvl(b.h_recdate,sysdate+999)-(1/48)
and a.oid = '''||V_ZMLX ||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
else ----如果没有找到记录就插入一条记录
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqdg_bd
select a.recorgid,a.recopid,a.oid,a.formnum,a.servnumber,a.subsid,a.groupoid,
a.HD_IDEN_NO,a.HD_CUST_NAME,a.contacttype,a.recdefid,a.SUBRECDEFID,
a.q_recdate,a.recdate,a.h_recdate,a.ipaddress
from shzc.wq_khsl_rsj_zbqdg a
where a.oid = '''||V_ZMLX ||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
end if;
END LOOP; --循环结束
--SQLSTMT0 := SQLSTMT0 || ' ';
--语句截取组成完整语句
--SQLSTMT1 := SUBSTR(SQLSTMT0 , 1 , length( SQLSTMT0 )-11) ;
--EXECUTE IMMEDIATE (SQLSTMT1);
end;
----------------新一轮--------------
---同工号时间段重叠部分单独提出
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdg_cd'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdg_cd as
select a.* from shzc.wq_khsl_rsj_zbqdg_bd a,
shzc.wq_khsl_rsj_zbqdg_bd b
where a.ipaddress=b.ipaddress
and a.recdate>b.recdate
and a.h_recdate<=b.h_recdate ';
EXECUTE IMMEDIATE (SQLSTMT0);
---剔除重叠部分
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdg_jg'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdg_jg as
select a.* from shzc.wq_khsl_rsj_zbqdg_bd a
where a.oid not in (
select b.oid from shzc.wq_khsl_rsj_zbqdg_cd b ) ';
EXECUTE IMMEDIATE (SQLSTMT0);
---------------按受理工号和号码排序，看是否1小时内
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdh'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdh as
select a.*,row_number() over (partition by a.ipaddress,a.servnumber order by a.recdate ,a.oid ) 排名
from shzc.wq_khsl_rsj_zbqdg_jg a
order by a.oid ';
EXECUTE IMMEDIATE (SQLSTMT0);
SQLSTMT0 := 'delete shzc.wq_khsl_rsj_zbqdh_bd a ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
DECLARE CURSOR ZMLX IS select a.oid from SHZC.wq_khsl_rsj_zbqdh a order by a.oid ;
BEGIN OPEN ZMLX;
--因为是插入，这个数据用不上
--SQLSTMT0 := 'create table SHZC.SHZC_JFBB_QXMC_'|| ZDYF ||' storage (initial 8m next 3m pctincrease 0) as';
LOOP FETCH ZMLX INTO V_ZMLX; --循环开始
EXIT WHEN ZMLX%NOTFOUND;
--1小时以内的改结束时间
select nvl((select a.servnumber from shzc.wq_khsl_rsj_zbqdh a,
shzc.wq_khsl_rsj_zbqdh b
where a.ipaddress=b.ipaddress
and a.servnumber=b.servnumber
and a.排名=b.排名-1
and a.recdate >= nvl(b.h_recdate,sysdate+999)-(1/48)
and a.oid=V_ZMLX ),'0') into gd_state
from dual;
if gd_state <>'0' then ----如果有对应记录就插入后一条结束时间
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqdh_bd
select a.recorgid,a.recopid,a.oid,a.formnum,a.servnumber,a.subsid,a.groupoid,
a.HD_IDEN_NO,a.HD_CUST_NAME,a.contacttype,a.recdefid,a.SUBRECDEFID,
a.q_recdate,a.recdate,b.h_recdate,a.ipaddress
from shzc.wq_khsl_rsj_zbqdh a,
shzc.wq_khsl_rsj_zbqdh b
where a.ipaddress=b.ipaddress
and a.servnumber=b.servnumber
and a.排名=b.排名-1
and a.recdate >= nvl(b.h_recdate,sysdate+999)-(1/48)
and a.oid = '''||V_ZMLX ||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
else ----如果没有找到记录就插入一条记录
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqdh_bd
select a.recorgid,a.recopid,a.oid,a.formnum,a.servnumber,a.subsid,a.groupoid,
a.HD_IDEN_NO,a.HD_CUST_NAME,a.contacttype,a.recdefid,a.SUBRECDEFID,
a.q_recdate,a.recdate,a.h_recdate,a.ipaddress
from shzc.wq_khsl_rsj_zbqdh a
where a.oid = '''||V_ZMLX ||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
end if;
END LOOP; --循环结束
--SQLSTMT0 := SQLSTMT0 || ' ';
--语句截取组成完整语句
--SQLSTMT1 := SUBSTR(SQLSTMT0 , 1 , length( SQLSTMT0 )-11) ;
--EXECUTE IMMEDIATE (SQLSTMT1);
end;
----------------新一轮--------------
---同工号时间段重叠部分单独提出
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdh_cd'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdh_cd as
select a.* from shzc.wq_khsl_rsj_zbqdh_bd a,
shzc.wq_khsl_rsj_zbqdh_bd b
where a.ipaddress=b.ipaddress
and a.recdate>b.recdate
and a.h_recdate<=b.h_recdate ';
EXECUTE IMMEDIATE (SQLSTMT0);
---剔除重叠部分
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdh_jg'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdh_jg as
select a.* from shzc.wq_khsl_rsj_zbqdh_bd a
where a.oid not in (
select b.oid from shzc.wq_khsl_rsj_zbqdh_cd b ) ';
EXECUTE IMMEDIATE (SQLSTMT0);
---------------按受理工号和号码排序，看是否2小时内
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdi'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdi as
select a.*,row_number() over (partition by a.ipaddress,a.servnumber order by a.recdate ,a.oid ) 排名
from shzc.wq_khsl_rsj_zbqdh_jg a
order by a.oid ';
EXECUTE IMMEDIATE (SQLSTMT0);
SQLSTMT0 := 'delete shzc.wq_khsl_rsj_zbqdi_bd a ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
DECLARE CURSOR ZMLX IS select a.oid from SHZC.wq_khsl_rsj_zbqdi a order by a.oid ;
BEGIN OPEN ZMLX;
--因为是插入，这个数据用不上
--SQLSTMT0 := 'create table SHZC.SHZC_JFBB_QXMC_'|| ZDYF ||' storage (initial 8m next 3m pctincrease 0) as';
LOOP FETCH ZMLX INTO V_ZMLX; --循环开始
EXIT WHEN ZMLX%NOTFOUND;
--1小时以内的改结束时间
select nvl((select a.servnumber from shzc.wq_khsl_rsj_zbqdi a,
shzc.wq_khsl_rsj_zbqdi b
where a.ipaddress=b.ipaddress
and a.servnumber=b.servnumber
and a.排名=b.排名-1
and a.recdate >= nvl(b.h_recdate,sysdate+999)-(1/48)
and a.oid=V_ZMLX ),'0') into gd_state
from dual;
if gd_state <>'0' then ----如果有对应记录就插入后一条结束时间
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqdi_bd
select a.recorgid,a.recopid,a.oid,a.formnum,a.servnumber,a.subsid,a.groupoid,
a.HD_IDEN_NO,a.HD_CUST_NAME,a.contacttype,a.recdefid,a.SUBRECDEFID,
a.q_recdate,a.recdate,b.h_recdate,a.ipaddress
from shzc.wq_khsl_rsj_zbqdi a,
shzc.wq_khsl_rsj_zbqdi b
where a.ipaddress=b.ipaddress
and a.servnumber=b.servnumber
and a.排名=b.排名-1
and a.recdate >= nvl(b.h_recdate,sysdate+999)-(1/48)
and a.oid = '''||V_ZMLX ||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
else ----如果没有找到记录就插入一条记录
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqdi_bd
select a.recorgid,a.recopid,a.oid,a.formnum,a.servnumber,a.subsid,a.groupoid,
a.HD_IDEN_NO,a.HD_CUST_NAME,a.contacttype,a.recdefid,a.SUBRECDEFID,
a.q_recdate,a.recdate,a.h_recdate,a.ipaddress
from shzc.wq_khsl_rsj_zbqdi a
where a.oid = '''||V_ZMLX ||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
end if;
END LOOP; --循环结束
--SQLSTMT0 := SQLSTMT0 || ' ';
--语句截取组成完整语句
--SQLSTMT1 := SUBSTR(SQLSTMT0 , 1 , length( SQLSTMT0 )-11) ;
--EXECUTE IMMEDIATE (SQLSTMT1);
end;
-----------------
----------------新一轮--------------
---同工号时间段重叠部分单独提出
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdi_cd'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdi_cd as
select a.* from shzc.wq_khsl_rsj_zbqdi_bd a,
shzc.wq_khsl_rsj_zbqdi_bd b
where a.ipaddress=b.ipaddress
and a.recdate>b.recdate
and a.h_recdate<=b.h_recdate ';
EXECUTE IMMEDIATE (SQLSTMT0);
---剔除重叠部分
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdi_jg'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdi_jg as
select a.* from shzc.wq_khsl_rsj_zbqdi_bd a
where a.oid not in (
select b.oid from shzc.wq_khsl_rsj_zbqdi_cd b ) ';
EXECUTE IMMEDIATE (SQLSTMT0);
---------------按受理工号和号码排序，看是否2小时内
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdj'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdj as
select a.*,row_number() over (partition by a.ipaddress,a.servnumber order by a.recdate ,a.oid ) 排名
from shzc.wq_khsl_rsj_zbqdi_jg a
order by a.oid ';
EXECUTE IMMEDIATE (SQLSTMT0);
SQLSTMT0 := 'delete shzc.wq_khsl_rsj_zbqdj_bd a ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
DECLARE CURSOR ZMLX IS select a.oid from SHZC.wq_khsl_rsj_zbqdj a order by a.oid ;
BEGIN OPEN ZMLX;
--因为是插入，这个数据用不上
--SQLSTMT0 := 'create table SHZC.SHZC_JFBB_QXMC_'|| ZDYF ||' storage (initial 8m next 3m pctincrease 0) as';
LOOP FETCH ZMLX INTO V_ZMLX; --循环开始
EXIT WHEN ZMLX%NOTFOUND;
--1小时以内的改结束时间
select nvl((select a.servnumber from shzc.wq_khsl_rsj_zbqdj a,
shzc.wq_khsl_rsj_zbqdj b
where a.ipaddress=b.ipaddress
and a.servnumber=b.servnumber
and a.排名=b.排名-1
and a.recdate >= nvl(b.h_recdate,sysdate+999)-(1/48)
and a.oid=V_ZMLX ),'0') into gd_state
from dual;
if gd_state <>'0' then ----如果有对应记录就插入后一条结束时间
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqdj_bd
select a.recorgid,a.recopid,a.oid,a.formnum,a.servnumber,a.subsid,a.groupoid,
a.HD_IDEN_NO,a.HD_CUST_NAME,a.contacttype,a.recdefid,a.SUBRECDEFID,
a.q_recdate,a.recdate,b.h_recdate,a.ipaddress
from shzc.wq_khsl_rsj_zbqdj a,
shzc.wq_khsl_rsj_zbqdj b
where a.ipaddress=b.ipaddress
and a.servnumber=b.servnumber
and a.排名=b.排名-1
and a.recdate >= nvl(b.h_recdate,sysdate+999)-(1/48)
and a.oid = '''||V_ZMLX ||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
else ----如果没有找到记录就插入一条记录
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqdj_bd
select a.recorgid,a.recopid,a.oid,a.formnum,a.servnumber,a.subsid,a.groupoid,
a.HD_IDEN_NO,a.HD_CUST_NAME,a.contacttype,a.recdefid,a.SUBRECDEFID,
a.q_recdate,a.recdate,a.h_recdate,a.ipaddress
from shzc.wq_khsl_rsj_zbqdj a
where a.oid = '''||V_ZMLX ||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
end if;
END LOOP; --循环结束
--SQLSTMT0 := SQLSTMT0 || ' ';
--语句截取组成完整语句
--SQLSTMT1 := SUBSTR(SQLSTMT0 , 1 , length( SQLSTMT0 )-11) ;
--EXECUTE IMMEDIATE (SQLSTMT1);
end;
-----------------
---同工号时间段重叠部分单独提出
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdj_cd'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdj_cd as
select a.* from shzc.wq_khsl_rsj_zbqdj_bd a,
shzc.wq_khsl_rsj_zbqdj_bd b
where a.ipaddress=b.ipaddress
and a.recdate>b.recdate
and a.h_recdate<=b.h_recdate ';
EXECUTE IMMEDIATE (SQLSTMT0);
---剔除重叠部分
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdj_jg'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdj_jg as
select a.* from shzc.wq_khsl_rsj_zbqdj_bd a
where a.oid not in (
select b.oid from shzc.wq_khsl_rsj_zbqdj_cd b ) ';
EXECUTE IMMEDIATE (SQLSTMT0);
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqdj_jgs'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqdj_jgs as
select distinct a.recorgid,a.recopid,a.oid,a.formnum,a.servnumber,
a.subsid,a.groupoid,a.hd_iden_no,a.hd_cust_name,a.contacttype,
a.recdefid,a.subrecdefid,a.q_recdate,a.recdate,a.h_recdate,a.ipaddress
from shzc.wq_khsl_rsj_zbqdj_jg a ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
-----------------------预警数据：按附件中IP地址（匹配上台席名称）提取数据，剔除后台工号，
--剔除前业务办理结束时间12：00以后、后业务办理结束时间13：30以前的数据，最后保留“单业务超30分钟-120分钟、等候时长超30分钟-120分钟”的数据。
--shzc.wq_khsl_rsj_zbqd_yjtx 预警台席
SQLSTMT0 := 'delete shzc.wq_khsl_rsj_zbqd_yjtxt_bd a where cycle='''||p_day||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqd_yjtxt_bd
select to_char(a.recdate,''yyyymmdd'') cycle,substr(a.recorgid,8,1) qx,count(distinct a.recorgid) recorgids,count(distinct a.ipaddress) ipaddresss
from SHZC.wq_khsl_rsj_zbqda a,
shzc.wq_khsl_rsj_zbqd_yjtx b
where a.ipaddress=b.ip
group by to_char(a.recdate,''yyyymmdd''),substr(a.recorgid,8,1)
union all
select to_char(a.recdate,''yyyymmdd'') cycle,''q'' qx,count(distinct a.recorgid) recorgids,count(distinct a.ipaddress) ipaddresss
from SHZC.wq_khsl_rsj_zbqda a,
shzc.wq_khsl_rsj_zbqd_yjtx b
where a.ipaddress=b.ip
group by to_char(a.recdate,''yyyymmdd'') ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqd_yjtxt'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqd_yjtxt as
select substr(a.orgid,8,1) qx,a.区县,count(distinct a.orgid) orgids,count(distinct a.ip) ips
from shzc.wq_khsl_rsj_zbqd_yjtx a
group by substr(a.orgid,8,1),a.区县
union all
select ''q'' qx,''全市'',count(distinct a.orgid) orgids,count(distinct a.ip) ips
from shzc.wq_khsl_rsj_zbqd_yjtx a ';
EXECUTE IMMEDIATE (SQLSTMT0);
---营业厅开放座席
SQLSTMT0 := 'delete shzc.wq_khsl_rsj_zbqd_yjtxy_bd a where cycle='''||p_day||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqd_yjtxy_bd
select to_char(a.recdate,''yyyymmdd'') cycle,a.recorgid,count(distinct a.recorgid) recorgids,count(distinct a.ipaddress) ipaddresss
from SHZC.wq_khsl_rsj_zbqda a,
shzc.wq_khsl_rsj_zbqd_yjtx b
where a.ipaddress=b.ip
group by to_char(a.recdate,''yyyymmdd''),a.recorgid ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqd_yjsja'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqd_yjsja as
select a.recorgid,t.orgname,
a.recopid,
a.ipaddress,tx.区县,tx.营业厅,tx.台席名称,
a.oid,
a.formnum,
a.servnumber,
a.subsid,
a.groupoid,
a.hd_iden_no,
a.hd_cust_name,
a.contacttype,c.dictname,
a.recdefid,b.itemname,
a.subrecdefid,
a.q_recdate,
a.recdate,
a.h_recdate,
round((a.recdate-a.q_recdate)*24*60,1) 等候时长 ,
round((a.h_recdate-a.recdate)*24*60,1) 单业务时长
from shzc.wq_khsl_rsj_zbqdj_jgs a,
tbcs.organization@bcv t,
tbcs.reception_define@bcv b,
(select * from tbcs.dict_item@bcv where groupid=''bsac'') c,
--shzc.wq_khsl_rsj_zbqd_yjtx 预警台席
shzc.wq_khsl_rsj_zbqd_yjtx tx
where a.recorgid=t.orgid(+)
and a.recdefid=b.itemid
and a.contacttype =c.dictid(+)
and a.ipaddress=tx.ip ';
EXECUTE IMMEDIATE (SQLSTMT0);
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqd_yjsjb'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqd_yjsjb as
select a.*,
--如果前一笔业务12点前，受理是在12点后，减去12点内时间
round(nvl(case when to_char(a.q_recdate,''hh24mi'')<=''1200'' and to_char(a.recdate,''hh24mi'')>=''1330''
then ((a.recdate-to_date(to_char(a.recdate,''yyyymmdd'')||''1200'',''yyyymmddhh24mi'')) - (a.recdate-to_date(to_char(a.recdate,''yyyymmdd'')||''1330'',''yyyymmddhh24mi'')))*24*60
when to_char(a.q_recdate,''hh24mi'')<=''1200'' and to_char(a.recdate,''hh24mi'')>=''1200'' and to_char(a.recdate,''hh24mi'')<=''1330''
then ((a.recdate-to_date(to_char(a.recdate,''yyyymmdd'')||''1200'',''yyyymmddhh24mi'')) )*24*60
when to_char(a.q_recdate,''hh24mi'')>=''1200'' and to_char(a.q_recdate,''hh24mi'')<=''1330'' and to_char(a.recdate,''hh24mi'')<=''1330''
then ((a.recdate-a.q_recdate) )*24*60
when to_char(a.q_recdate,''hh24mi'')>=''1200'' and to_char(a.q_recdate,''hh24mi'')<=''1330'' and to_char(a.recdate,''hh24mi'')>=''1330''
then ((to_date(to_char(a.recdate,''yyyymmdd'')||''1330'',''yyyymmddhh24mi'')-a.q_recdate) )*24*60 end,0),1) 等候时长休息,
--如果业务12点前，最后受理是在12点后，减去12点内时间
round(nvl(case when to_char(a.recdate,''hh24mi'')<=''1200'' and to_char(a.h_recdate,''hh24mi'')>=''1330''
then ((a.h_recdate-to_date(to_char(a.recdate,''yyyymmdd'')||''1200'',''yyyymmddhh24mi'')) - (a.h_recdate-to_date(to_char(a.recdate,''yyyymmdd'')||''1330'',''yyyymmddhh24mi'')))*24*60
when to_char(a.recdate,''hh24mi'')<=''1200'' and to_char(a.h_recdate,''hh24mi'')>=''1200'' and to_char(a.h_recdate,''hh24mi'')<=''1330''
then ((a.h_recdate-to_date(to_char(a.recdate,''yyyymmdd'')||''1200'',''yyyymmddhh24mi'')) )*24*60
when to_char(a.recdate,''hh24mi'')>=''1200'' and to_char(a.recdate,''hh24mi'')<=''1330'' and to_char(a.h_recdate,''hh24mi'')<=''1330''
then ((a.h_recdate-a.recdate) )*24*60
when to_char(a.recdate,''hh24mi'')>=''1200'' and to_char(a.recdate,''hh24mi'')<=''1330'' and to_char(a.h_recdate,''hh24mi'')>=''1330''
then (to_date(to_char(a.recdate,''yyyymmdd'')||''1330'',''yyyymmddhh24mi'')-a.recdate )*24*60 end,0),1) 单业务时长休息
from shzc.wq_khsl_rsj_zbqd_yjsja a ';
EXECUTE IMMEDIATE (SQLSTMT0);
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqd_yjsjc'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqd_yjsjc as
select a.*,nvl(case
when a.等候时长-a.等候时长休息>=30 and a.单业务时长-a.单业务时长休息>=30 then ''等候与办理时长''
when a.等候时长-a.等候时长休息>=30 then ''等候时长''
when a.单业务时长-a.单业务时长休息>=30 then ''单业务时长'' end ,''正常'') 是否超长
from shzc.wq_khsl_rsj_zbqd_yjsjb a ';
EXECUTE IMMEDIATE (SQLSTMT0);
---2022-0623 业务汇总
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqd_yjsjd'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqd_yjsjd as
select distinct b.oid oidzd,a.* from shzc.wq_khsl_rsj_zbqda_cpywb a,
shzc.wq_khsl_rsj_zbqd_yjsjc b
where a.servnumber=b.servnumber
and a.ipaddress=b.ipaddress
and a.recdate>=b.recdate
and a.recdate<=b.h_recdate ';
EXECUTE IMMEDIATE (SQLSTMT0);
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqd_yjsje'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqd_yjsje as
select a.oidzd,a.itemname,count(*) synum,row_number() over (partition by a.oidzd order by count(*) desc,min(a.recdate) ) 排名
from shzc.wq_khsl_rsj_zbqd_yjsjd a
group by a.oidzd,a.itemname ';
EXECUTE IMMEDIATE (SQLSTMT0);
zhyw.shc_drop_retable(upper('wq_khsl_rsj_zbqd_yjsjf'),'SHZC');
SQLSTMT0 := 'create table shzc.wq_khsl_rsj_zbqd_yjsjf as
select a.oidzd,listagg (a.itemname,'','') within group (order by a.oidzd) itemname
from shzc.wq_khsl_rsj_zbqd_yjsje a
group by a.oidzd ';
EXECUTE IMMEDIATE (SQLSTMT0);
SQLSTMT0 := 'delete shzc.wq_khsl_rsj_zbqd_yjsj_bd a where to_char(a.recdate,''yyyymmdd'')='''||p_day||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
---------2022--0620-预警数据 结果存档
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqd_yjsj_bd
select a.*,b.itemname itemnames from shzc.wq_khsl_rsj_zbqd_yjsjc a,
shzc.wq_khsl_rsj_zbqd_yjsjf b
where a.oid=b.oidzd(+) ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
------异常数据存档
SQLSTMT0 := 'delete shzc.wq_khsl_rsj_zbqd_yssj_yccd a where to_char(a.recdate,''yyyymmdd'')='''||p_day||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqd_yssj_yccd
select distinct a.* from shzc.wq_khsl_rsj_zbqd_yssj_bd a where a.是否超长<>''正常''
and not exists (select 1 from shzc.wq_khsl_rsj_zbqd_yssj_yccd t where t.oid=a.oid) ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
SQLSTMT0 := 'delete shzc.wq_khsl_rsj_zbqd_yjsj_yccd a where to_char(a.recdate,''yyyymmdd'')='''||p_day||''' ';
EXECUTE IMMEDIATE (SQLSTMT0);
SQLSTMT0 := 'insert into shzc.wq_khsl_rsj_zbqd_yjsj_yccd
select distinct a.* from shzc.wq_khsl_rsj_zbqd_yjsj_bd a where a.是否超长<>''正常''
and a.等候时长休息+a.单业务时长休息<=0
and a.等候时长<=120
and a.单业务时长<=120
and not exists (select 1 from shzc.wq_khsl_rsj_zbqd_yjsj_yccd t where t.oid=a.oid) ';
EXECUTE IMMEDIATE (SQLSTMT0);
commit;
扫一扫
03-16
1万+
1万+
04-16
1290
1290
02-27
964
964
09-23
911
911
04-16
544
544
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
