create or replace procedure biller533.shzc_202106_xshhr_ywmx
(p_rq varchar2,p_qx in varchar2,p_userid varchar2, p_xzlx varchar2,
p_cursor in out Results.ref_cursor_type)
as
v_qx VARCHAR2(10);
v_rq VARCHAR2(80);
v_yy VARCHAR2(80);
v_xzlx VARCHAR2(80);
SQL_STRING VARCHAR2(20000);
v_sql VARCHAR2(20000);
v_hd VARCHAR2(100);
begin
v_rq := substr( p_rq,1,8);
v_yy := substr( p_rq,1,6);
v_xzlx := trim( p_xzlx);
----权限的控制 开始
select max(qx) into v_qx from zhyw.zibo_jyfx_staff where trim(userid)=p_userid;
select name1 into v_qx from zhyw.rpt_county where county_id=v_qx;
----2021-0617
–业务类型
–“充值、和家庭产品、流量包、魔百和点播权益年包、套餐流量升档”除外
–业务名称
–“和彩云普通会员免费3个月、和彩云普通会员、5G盲盒免费体验、车主服务”除外
fan_drop_retable(upper('dxq_hhr_tjhh_mdpx_slbd_ta'),'ZHYW');
SQL_STRING:='create table zhyw.dxq_hhr_tjhh_mdpx_slbd_ta as
select * from shzc.dxq_hhr_tjhh_mdpx_slbd a
where 办理状态=’‘成功’’ and a.操作员工号=’‘ca22f00a’’
and a.业务类型 not in (’‘充值’’,’‘和家庭产品’’,’‘流量包’’,’‘魔百和点播权益年包’’,’‘套餐流量升档’’)
and a.业务名称 not in (’‘和彩云普通会员免费3个月’’,’‘和彩云普通会员’’,’‘5G盲盒免费体验’’,’‘车主服务’’) ’ ;
execute immediate (SQL_STRING);
—积分倍享 特殊处理
SQL_STRING:=‘update zhyw.dxq_hhr_tjhh_mdpx_slbd_ta a set a.业务编码=’‘gl.base.jfbxhy.500’’
where a.业务编码 like ‘’%积分倍享%’’ ’ ;
execute immediate (SQL_STRING);
commit;
-----市场大体业务 select * from zhyw.dxq_hhr_tjhh_mdpx_slbd_yh
fan_drop_retable(upper('dxq_hhr_tjhh_mdpx_slbd_taa'),'ZHYW');
SQL_STRING:='create table zhyw.dxq_hhr_tjhh_mdpx_slbd_taa as
select a.,b.业务id,b.酬金额度 from zhyw.dxq_hhr_tjhh_mdpx_slbd_ta a,
zhyw.dxq_hhr_tjhh_mdpx_slbd_yh b
where a.业务名称=b.业务名称
and a.业务编码=b.业务id
union all
select a.,b.业务id,b.酬金额度 from zhyw.dxq_hhr_tjhh_mdpx_slbd_ta a,
zhyw.dxq_hhr_tjhh_mdpx_slbd_yh b
where a.业务名称=b.业务名称
and a.业务编码<>b.业务id ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tb’),‘ZHYW’);
SQL_STRING:='create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tb as
select a.业务类型,a.业务名称,a.业务id,count(*) 数量
from zhyw.dxq_hhr_tjhh_mdpx_slbd_taa a
group by a.业务类型,a.业务名称,a.业务id ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tc’),‘ZHYW’);
SQL_STRING:='create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tc as
select a.*,
case when b.privname is not null then b.privname
when c.itemname is not null then c.itemname
when pp.prodname is not null then pp.prodname end 业务名,
case when b.privname is not null then ‘‘privid’’
when c.itemname is not null then ‘‘service’’
when pp.prodname is not null then ‘‘prodid’’ end 业务类
from zhyw.dxq_hhr_tjhh_mdpx_slbd_tb a,
TBCS.privilege_SCHEME@BCV b,
tbcs.service@bcv c,
tbcs.product@bcv pp
where a.业务id=b.privid(+)
and a.业务id=c.itemid(+)
and a.业务id=pp.prodid(+) ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_td’),‘ZHYW’);
SQL_STRING:=‘create table zhyw.dxq_hhr_tjhh_mdpx_slbd_td as
select a.*,to_date(a.办理时间,’‘yyyy-mm-dd hh24:mi:ss’’) 办理时间date,
b.业务名,b.业务类 from zhyw.dxq_hhr_tjhh_mdpx_slbd_taa a,
(select * from zhyw.dxq_hhr_tjhh_mdpx_slbd_tc b where b.业务名 is not null ) b
where a.业务类型=b.业务类型
and a.业务名称=b.业务名称
and a.业务id=b.业务id ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_te’),‘ZHYW’);
SQL_STRING:=‘create table zhyw.dxq_hhr_tjhh_mdpx_slbd_te as
select a.* from
(select a.办理号码,a.办理时间date,b.subsid,b.createdate,b.status,
row_number() over (partition by a.办理号码,a.办理时间date order by b.createdate desc ) 排名
from zhyw.dxq_hhr_tjhh_mdpx_slbd_td a,
(select * from zhyw.subscriber b where b.status not in (’‘US26’’,’‘US28’’) ) B
where a.办理号码=b.servnumber
and a.办理时间date>=b.createdate) a
where 排名=1’ ;
execute immediate (SQL_STRING);
-----号段
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tf’),‘ZHYW’);
SQL_STRING:=‘create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tf as
select a.*,t.subsid,t.createdate,t.status,
nvl(b.省名,d.省名)省名,
nvl(b.地市,d.地市)地市
from zhyw.dxq_hhr_tjhh_mdpx_slbd_td a,
zhyw.dxq_hhr_tjhh_mdpx_slbd_te t,
shzc.qghdb_hdq8w b,
shzc.qghdb_hdq7w d
where a.办理号码=t.办理号码(+)
and a.办理时间date=t.办理时间date(+)
and substr(a.办理号码,1,8)=b.号段(+)
and substr(a.办理号码,1,7)=d.号段(+)’ ;
execute immediate (SQL_STRING);
----产品
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tfa’),‘ZHYW’);
SQL_STRING:=‘create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tfa as
select distinct a.subsid,a.prodid,a.startdate,a.enddate,a.applyoid,a.applyoperid,a.canceloid,b.办理时间date
from zhyw.subs_privilege a,
(select * from zhyw.dxq_hhr_tjhh_mdpx_slbd_tf b where b.业务类=’‘prodid’’ and b.subsid is not null ) b
where a.subsid=b.subsid
and a.prodid=b.业务id
and APPLYOPERID=’‘ca22f00a’’ ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tfad’),‘ZHYW’);
SQL_STRING:=‘create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tfad as
select distinct a.subsid,b.业务id,a.startdate,a.enddate,a.applyoid,a.applyoperid,a.canceloid,b.办理时间date
from zhyw.subs_privilege a,
(select b.*,c.prodid from zhyw.dxq_hhr_tjhh_mdpx_slbd_tf b ,
tbcs.Product_Solution_Prods@bcv c
where b.业务类=’‘prodid’’ and b.subsid is not null and b.业务id=c.SOLUTIONID ) b
where a.subsid=b.subsid
and a.prodid=b.prodid
and APPLYOPERID=’‘ca22f00a’’ ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tfap’),‘ZHYW’);
SQL_STRING:=‘create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tfap as
select a.* from
(select a.*,row_number() over (partition by a.subsid,a.办理时间date order by a.startdate /desc/ ) 排名
from ( select * from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfa a
union all
select * from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfad a ) a
where a.startdate>=a.办理时间date ) a
where 排名=1’ ;
execute immediate (SQL_STRING);
----优惠
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tfb’),‘ZHYW’);
SQL_STRING:=‘create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tfb as
select a.subsid,a.privid,min(a.startdate) startdate,max(a.enddate) enddate,
max(a.applyoid) applyoid,max(a.applyoperid) applyoperid,max(a.canceloid) canceloid,max(b.办理时间date) 办理时间date
from zhyw.subs_privilege a,
(select * from zhyw.dxq_hhr_tjhh_mdpx_slbd_tf b where b.业务类=’‘privid’’ and b.subsid is not null ) b
where a.subsid=b.subsid
and a.privid=b.业务id
and APPLYOPERID=’‘ca22f00a’’
group by a.subsid,a.privid ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tfbb’),‘ZHYW’);
SQL_STRING:=‘create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tfbb as
select a.subsid,a.prodid,min(a.startdate) startdate,max(a.enddate) enddate,
max(a.applyoid) applyoid,max(a.applyoperid) applyoperid,max(a.canceloid) canceloid,max(b.办理时间date) 办理时间date
from zhyw.subs_privilege a,
(select * from zhyw.dxq_hhr_tjhh_mdpx_slbd_tf b where b.业务类=’‘privid’’ and b.subsid is not null ) b
where a.subsid=b.subsid
and a.prodid=b.业务id
and APPLYOPERID=’‘ca22f00a’’
group by a.subsid,a.prodid ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tfbp’),‘ZHYW’);
SQL_STRING:='create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tfbp as
select a.* from
(select a.*,row_number() over (partition by a.subsid,a.办理时间date order by a.startdate /desc/ ) 排名
from (select * from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfb a
union all
select * from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfbb a ) a
where a.startdate>=a.办理时间date ) a
where 排名=1 ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tfc’),‘ZHYW’);
SQL_STRING:=‘create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tfc as
select distinct a.* from
(select a.applyoid from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfa a where nvl(a.applyoid,’‘0’’) <>’‘0’’
union all
select a.canceloid from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfa a where nvl(a.canceloid ,’‘0’’) <>’‘0’’
union all
select a.applyoid from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfad a where nvl(a.applyoid,’‘0’’) <>’‘0’’
union all
select a.canceloid from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfad a where nvl(a.canceloid ,’‘0’’) <>’‘0’’
union all
select a.applyoid from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfb a where nvl(a.applyoid ,’‘0’’) <>’‘0’’
union all
select a.canceloid from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfb a where nvl(a.canceloid ,’‘0’’) <>’‘0’’
union all
select a.applyoid from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfbb a where nvl(a.applyoid ,’‘0’’) <>’‘0’’
union all
select a.canceloid from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfbb a where nvl(a.canceloid ,’‘0’’) <>’‘0’’
) a ’ ;
execute immediate (SQL_STRING);
SQL_STRING:='insert into zhyw.dxq_hhr_tjhh_mdpx_slbd_tfd
select a.oid,a.entitytype,a.entityid,a.contacttype,a.servnumber,a.recdefid,
a.recorgid,a.recopid,a.recdate,a.isrollback,a.status
from zhyw.shc_reception a,
zhyw.dxq_hhr_tjhh_mdpx_slbd_tfc b
where a.oid=b.applyoid
and not exists (select 1 from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfd t where t.oid=a.oid) ’ ;
execute immediate (SQL_STRING);
commit;
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tfe’),‘ZHYW’);
SQL_STRING:='create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tfe as
select distinct a.*
from
(select * from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfap a
union all
select * from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfbp a ) a ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tff’),‘ZHYW’);
SQL_STRING:='create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tff as
select a.subsid,a.prodid,a.办理时间date,a.startdate,a.enddate,
a.applyoid,b.recorgid orgid_sl,b1.orgname orgname_sl,b.recopid recopid_sl,b2.opername opername_sl,b.recdate recdate_sl,
a.canceloid,c.recorgid orgid_qx,c1.orgname orgname_qx,c.recopid recopid_qx,c2.opername opername_qx,c.recdate recdate_qx
from zhyw.dxq_hhr_tjhh_mdpx_slbd_tfe a,
zhyw.dxq_hhr_tjhh_mdpx_slbd_tfd b,
tbcs.organization@bcv b1,
tbcs.operator@bcv b2,
zhyw.dxq_hhr_tjhh_mdpx_slbd_tfd c,
tbcs.organization@bcv c1,
tbcs.operator@bcv c2
where a.applyoid=b.oid(+)
and b.recorgid=b1.orgid(+)
and b.recopid=b2.operid(+)
and a.canceloid=c.oid(+)
and c.recorgid=c1.orgid(+)
and c.recopid=c2.operid(+) ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tg’),‘ZHYW’);
SQL_STRING:='create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tg as
select a.*,b.startdate,b.enddate,
b.applyoid,b.ORGID_SL ,b.ORGNAME_SL ,b.RECOPID_SL ,b.OPERNAME_SL ,
b.RECDATE_SL ,b.CANCELOID ,b.ORGID_QX ,b.ORGNAME_QX ,b.RECOPID_QX ,
b.OPERNAME_QX ,b.RECDATE_QX
from zhyw.dxq_hhr_tjhh_mdpx_slbd_tf a,
zhyw.dxq_hhr_tjhh_mdpx_slbd_tff b
where a.subsid is not null
and a.subsid=b.subsid(+)
and a.业务id=b.prodid(+)
and a.办理时间date=b.办理时间date(+) ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tgx’),‘ZHYW’);
SQL_STRING:='create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tgx as
select a.* from
(select a.*,row_number() over (partition by a.合伙人手机号 order by a.in_time desc,a.招募时间 ) 排名
from shzc.dxq_hhr_tjhh_mdpx a ) a
where 排名=1 ’ ;
execute immediate (SQL_STRING);
fan_drop_retable(upper(‘dxq_hhr_tjhh_mdpx_slbd_tga’),‘ZHYW’);
SQL_STRING:='create table zhyw.dxq_hhr_tjhh_mdpx_slbd_tga as
select distinct nvl(q.name,a.区县) 区县,a.订单号,a.业务类型,a.业务名称,a.业务编码,a.业务id,a.酬金额度,
a.办理号码,a.subsid,b.createdate,b.status,b.statusdate,
a.办理时间date,a.startdate,a.enddate,a.recdate_qx,a.orgid_qx,a.orgname_qx,a.recopid_qx,a.opername_qx,
a.操作员手机号,a.操作员姓名, d.推荐人姓名,d.推荐人工号, c1.orgid 推荐人组织,
e1.orgname 推荐人组织名,d1.unit_id,d1.unit_name,d1.微网格编码,d1.微网格名称
from zhyw.dxq_hhr_tjhh_mdpx_slbd_tg a,
zhyw.subscriber b,
zhyw.dxq_hhr_tjhh_mdpx_slbd_tgx d,
tbcs.operator@bcv c1,
tbcs.organization@bcv e1,
zhyw.shc_organization d1,
zhyw.rpt_county q
where a.subsid=b.subsid
and a.操作员手机号=d.合伙人手机号(+)
and d.推荐人工号=c1.operid(+)
and c1.orgid=e1.orgid(+)
and c1.orgid =d1.orgid(+)
and substr(c1.orgid,8,1)=q.county_id(+) ’ ;
execute immediate (SQL_STRING);
if v_xzlx=‘明细’ then
if v_qx=‘全市’ then
if p_qx=‘全市’ then
v_sql:=’ select ‘‘区县’’, ‘‘订单号’’, ‘‘业务类型’’, ‘‘业务名称’’, ‘‘酬金额度’’, ‘‘办理号码’’, ‘‘用户状态’’, ‘‘状态时间’’,
‘‘办理时间’’, ‘‘生效时间’’, ‘‘失效时间’’, ‘‘取消受理时间’’, ‘‘取消受理单位’’, ‘‘取消受理工号’’, ‘‘取消受理人员’’,
‘‘操作员手机号’’, ‘‘操作员姓名’’, ‘‘推荐人工号’’, ‘‘推荐人组织名’’, ‘‘营销区域’’, ‘‘微网格名称’’
from dual union all
select * from (select a.区县,a.订单号,a.业务类型,a.业务名称,to_char(a.酬金额度),a.办理号码,zt.dictname,to_char(a.statusdate,’‘yyyymmdd’’) ,
to_char(a.办理时间date,’‘yyyymmdd’’),to_char(a.startdate,’‘yyyymmdd’’),to_char(a.enddate,’‘yyyymmdd’’),
to_char(a.recdate_qx,’‘yyyymmdd’’),a.orgname_qx,a.recopid_qx,a.opername_qx,
a.操作员手机号,a.操作员姓名,a.推荐人工号,a.推荐人组织名,a.unit_name,a.微网格名称
from zhyw.dxq_hhr_tjhh_mdpx_slbd_tga a,
(select * from tbcs.dict_item@bcv where groupid=’‘US’’) zt
where a.status=zt.dictid(+)
and to_char(a.办理时间date,’‘yyyymm’’)=’’’||v_yy||’’’
and to_char(a.办理时间date,’‘yyyymmdd’’)<=’’’||v_rq||’’’
order by a.办理时间date desc ) a ’ ;
else
v_sql:=‘select ‘‘区县’’, ‘‘订单号’’, ‘‘业务类型’’, ‘‘业务名称’’, ‘‘酬金额度’’, ‘‘办理号码’’, ‘‘用户状态’’, ‘‘状态时间’’,
‘‘办理时间’’, ‘‘生效时间’’, ‘‘失效时间’’, ‘‘取消受理时间’’, ‘‘取消受理单位’’, ‘‘取消受理工号’’, ‘‘取消受理人员’’,
‘‘操作员手机号’’, ‘‘操作员姓名’’, ‘‘推荐人工号’’, ‘‘推荐人组织名’’, ‘‘营销区域’’, ‘‘微网格名称’’
from dual union all
select * from (select a.区县,a.订单号,a.业务类型,a.业务名称,to_char(a.酬金额度),a.办理号码,zt.dictname,to_char(a.statusdate,’‘yyyymmdd’’) ,
to_char(a.办理时间date,’‘yyyymmdd’’),to_char(a.startdate,’‘yyyymmdd’’),to_char(a.enddate,’‘yyyymmdd’’),
to_char(a.recdate_qx,’‘yyyymmdd’’),a.orgname_qx,a.recopid_qx,a.opername_qx,
a.操作员手机号,a.操作员姓名,a.推荐人工号,a.推荐人组织名,a.unit_name,a.微网格名称
from zhyw.dxq_hhr_tjhh_mdpx_slbd_tga a,
(select * from tbcs.dict_item@bcv where groupid=’‘US’’) zt
where a.status=zt.dictid(+)
and to_char(a.办理时间date,’‘yyyymm’’)=’’’||v_yy||’’’
and to_char(a.办理时间date,’‘yyyymmdd’’)<=’’’||v_rq||’’’
and 区县=’’’||p_qx||’’’
order by a.办理时间date desc ) a ’ ;
end if ;
elsif v_qx=p_qx then
v_sql:=’
select ‘‘区县’’, ‘‘订单号’’, ‘‘业务类型’’, ‘‘业务名称’’, ‘‘酬金额度’’, ‘‘办理号码’’, ‘‘用户状态’’, ‘‘状态时间’’,
‘‘办理时间’’, ‘‘生效时间’’, ‘‘失效时间’’, ‘‘取消受理时间’’, ‘‘取消受理单位’’, ‘‘取消受理工号’’, ‘‘取消受理人员’’,
‘‘操作员手机号’’, ‘‘操作员姓名’’, ‘‘推荐人工号’’, ‘‘推荐人组织名’’, ‘‘营销区域’’, ‘‘微网格名称’’
from dual union all
select * from (select a.区县,a.订单号,a.业务类型,a.业务名称,to_char(a.酬金额度),a.办理号码,zt.dictname,to_char(a.statusdate,’‘yyyymmdd’’) ,
to_char(a.办理时间date,’‘yyyymmdd’’),to_char(a.startdate,’‘yyyymmdd’’),to_char(a.enddate,’‘yyyymmdd’’),
to_char(a.recdate_qx,’‘yyyymmdd’’),a.orgname_qx,a.recopid_qx,a.opername_qx,
a.操作员手机号,a.操作员姓名,a.推荐人工号,a.推荐人组织名,a.unit_name,a.微网格名称
from zhyw.dxq_hhr_tjhh_mdpx_slbd_tga a,
(select * from tbcs.dict_item@bcv where groupid=’‘US’’) zt
where a.status=zt.dictid(+)
and to_char(a.办理时间date,’‘yyyymm’’)=’’’||v_yy||’’’
and to_char(a.办理时间date,’‘yyyymmdd’’)<=’’’||v_rq||’’’
and 区县=’’’||v_qx||’’’
order by a.办理时间date desc ) a ’ ;
end if;
elsif v_xzlx=‘汇总’ then
v_sql:='select ''区县'', ''推荐人组织'', ''有酬金业务量'', ''第1个月取消'', ''第2个月取消'', ''第3个月取消'',
''第4个月取消'', ''第5个月取消'', ''第6个月取消'', ''停机销号数'', ''留存率'' from dual union all
select 区县, 推荐人组织,to_char(有酬金业务量),to_char(第1个月取消),to_char(第2个月取消),to_char(第3个月取消),
to_char(第4个月取消),to_char(第5个月取消),to_char(第6个月取消),to_char(停机销号数),to_char(留存率)
from
(select a.区县,nvl(substr(a.推荐人组织,8,1),''a'') 推荐人组织,count(distinct a.订单号) 有酬金业务量,
count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 =1 and nvl(a.status,''US20'')=''US10'' then a.订单号 end) 第1个月取消,
count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 =2 and nvl(a.status,''US20'')=''US10'' then a.订单号 end) 第2个月取消,
count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 =3 and nvl(a.status,''US20'')=''US10'' then a.订单号 end) 第3个月取消,
count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 =4 and nvl(a.status,''US20'')=''US10'' then a.订单号 end) 第4个月取消,
count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 =5 and nvl(a.status,''US20'')=''US10'' then a.订单号 end) 第5个月取消,
count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 =6 and nvl(a.status,''US20'')=''US10'' then a.订单号 end) 第6个月取消,
count(distinct case when nvl(a.status,''US20'')<>''US10'' then a.subsid end) 停机销号数,
100-round((count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 in (1,2,3,4,5,6) and nvl(a.status,''US20'')=''US10'' then a.订单号 end)+
count(distinct case when nvl(a.status,''US20'')<>''US10'' then a.subsid end))/
count(distinct a.订单号)*100,2) 留存率
from zhyw.dxq_hhr_tjhh_mdpx_slbd_tga a
where a.酬金额度>0
group by a.区县,nvl(substr(a.推荐人组织,8,1),''a'')
union all
select ''全市'',''0'' 推荐人组织,count(distinct a.订单号) 有酬金业务量,
count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 =1 and nvl(a.status,''US20'')=''US10'' then a.订单号 end) 第1个月取消,
count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 =2 and nvl(a.status,''US20'')=''US10'' then a.订单号 end) 第2个月取消,
count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 =3 and nvl(a.status,''US20'')=''US10'' then a.订单号 end) 第3个月取消,
count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 =4 and nvl(a.status,''US20'')=''US10'' then a.订单号 end) 第4个月取消,
count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 =5 and nvl(a.status,''US20'')=''US10'' then a.订单号 end) 第5个月取消,
count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 =6 and nvl(a.status,''US20'')=''US10'' then a.订单号 end) 第6个月取消,
count(distinct case when nvl(a.status,''US20'')<>''US10'' then a.subsid end) 停机销号数,
100-round((count(distinct case when months_between(to_date(to_char(nvl(a.enddate,sysdate+9999),''yyyymm''),''yyyymm''),
to_date(to_char(a.startdate,''yyyymm''),''yyyymm''))+1 in (1,2,3,4,5,6) and nvl(a.status,''US20'')=''US10'' then a.订单号 end)+
count(distinct case when nvl(a.status,''US20'')<>''US10'' then a.subsid end))/
count(distinct a.订单号)*100,2) 留存率
from zhyw.dxq_hhr_tjhh_mdpx_slbd_tga a
where a.酬金额度>0 ) a ' ;
end if;
open p_cursor for v_sql;
end ;
扫一扫
6477
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
