select left(lcp.managecom,4) 分公司代码,(select name from ldcom where comcode=lcp.managecom) 机构名称,
(select labg.BranchAttr from LABranchGroup labg where labg.AgentGroup = lcp.AgentGroup)销售团队编码,
( select labg.Name BranchName from LABranchGroup labg where labg.AgentGroup = lcp.AgentGroup) 销售团队名称,lcp.appntname 投保人,lcp.insuredname 被保险人,lcp.contno 保单号,riskcode 险种代码,
lcp.cvalidate 生效时间,lcp.prem 保费,(select codename from ldcode where codetype='salechnl' and code=lcp.salechnl) 销售渠道,
(select groupagentcode from laagent where agentcode=lcp.agentcode) 业务员工号,
(select name from laagent where agentcode=lcp.agentcode) 业务员姓名,
(select
case
when la.branchtype = '1' and la.branchtype2 = '01' then '个险直销渠道'
when la.branchtype = '1' and la.branchtype2 = '02' then '个险中介渠道'
when la.branchtype = '1' and la.branchtype2 = '03' then '个险交叉销售渠道'
when la.branchtype = '1' and la.branchtype2 = '05' then '个险续收销售渠道'
when la.branchtype = '2' and la.branchtype2 = '01' then '团险直销渠道'
when la.branchtype = '2' and la.branchtype2 = '02' then '团险中介渠道'
when la.branchtype = '3' and la.branchtype2 = '01' then '银保渠道'
when la.branchtype = '4' and la.branchtype2 = '01' then '电话销售渠道'
when la.branchtype = '5' and la.branchtype2 = '01' then '互动渠道'
when la.branchtype = '6' and la.branchtype2 = '01' then '社保直销渠道'
when la.branchtype = '6' and la.branchtype2 = '02' then '社保综拓中介渠道'
when la.branchtype = '6' and la.branchtype2 = '03' then '社保中介渠道'
when la.branchtype = '7' and la.branchtype2 = '01' then '健管渠道'
when la.branchtype = '8' and la.branchtype2 = '02' then '电商渠道'
end
from lcappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName order by la.employdate desc
fetch first 1 rows only
) 投保人所在销售渠道,
(select la.groupagentcode from lcappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName order by la.employdate desc fetch first 1 rows only) 集团工号,
(select case when agentstate>='06' then '离职' else '在职' end from lcappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName and la.groupagentcode is not null order by la.employdate desc fetch first 1 rows only) 是否在职,
(select
case
when la.branchtype = '1' and la.branchtype2 = '01' then '个险直销渠道'
when la.branchtype = '1' and la.branchtype2 = '02' then '个险中介渠道'
when la.branchtype = '1' and la.branchtype2 = '03' then '个险交叉销售渠道'
when la.branchtype = '1' and la.branchtype2 = '05' then '个险续收销售渠道'
when la.branchtype = '2' and la.branchtype2 = '01' then '团险直销渠道'
when la.branchtype = '2' and la.branchtype2 = '02' then '团险中介渠道'
when la.branchtype = '3' and la.branchtype2 = '01' then '银保渠道'
when la.branchtype = '4' and la.branchtype2 = '01' then '电话销售渠道'
when la.branchtype = '5' and la.branchtype2 = '01' then '互动渠道'
when la.branchtype = '6' and la.branchtype2 = '01' then '社保直销渠道'
when la.branchtype = '6' and la.branchtype2 = '02' then '社保综拓中介渠道'
when la.branchtype = '6' and la.branchtype2 = '03' then '社保中介渠道'
when la.branchtype = '7' and la.branchtype2 = '01' then '健管渠道'
when la.branchtype = '8' and la.branchtype2 = '02' then '电商渠道'
end from lcinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name order by la.employdate desc fetch first 1 rows only
) 被保险人所在销售渠道,
(select groupagentcode from lcinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name order by la.employdate desc fetch first 1 rows only) 集团工号,
(select case when agentstate>='06' then '离职' else '在职' end
from lcinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name and la.groupagentcode is not null order by la.employdate desc fetch first 1 rows only) 是否在职
from lcpol lcp
where lcp.conttype='1' and lcp.appflag='1'
and lcp.cvalidate>='2025-06-01' and lcp.cvalidate<='2025-06-10' and salechnl IN ('04','13','14','15')
and exists (select 1 from lcappnt lc, laagent la WHERE lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName and la.agentstate<'06'
union select 1 from lcinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name and la.agentstate<'06') ---and la.agentstate<'06'在职
union
select left(lcp.managecom,4) 分公司代码,(select name from ldcom where comcode=lcp.managecom) 机构名称,
(select labg.BranchAttr from LABranchGroup labg where labg.AgentGroup = lcp.AgentGroup)销售团队编码,
( select labg.Name BranchName from LABranchGroup labg where labg.AgentGroup = lcp.AgentGroup) 销售团队名称,lcp.appntname 投保人,lcp.insuredname 被保险人,lcp.contno 保单号,riskcode 险种代码,
lcp.cvalidate 生效时间,lcp.prem 保费,(select codename from ldcode where codetype='salechnl' and code=lcp.salechnl) 销售渠道,
(select groupagentcode from laagent where agentcode=lcp.agentcode) 业务员工号,
(select name from laagent where agentcode=lcp.agentcode) 业务员姓名,
(select
case
when la.branchtype = '1' and la.branchtype2 = '01' then '个险直销渠道'
when la.branchtype = '1' and la.branchtype2 = '02' then '个险中介渠道'
when la.branchtype = '1' and la.branchtype2 = '03' then '个险交叉销售渠道'
when la.branchtype = '1' and la.branchtype2 = '05' then '个险续收销售渠道'
when la.branchtype = '2' and la.branchtype2 = '01' then '团险直销渠道'
when la.branchtype = '2' and la.branchtype2 = '02' then '团险中介渠道'
when la.branchtype = '3' and la.branchtype2 = '01' then '银保渠道'
when la.branchtype = '4' and la.branchtype2 = '01' then '电话销售渠道'
when la.branchtype = '5' and la.branchtype2 = '01' then '互动渠道'
when la.branchtype = '6' and la.branchtype2 = '01' then '社保直销渠道'
when la.branchtype = '6' and la.branchtype2 = '02' then '社保综拓中介渠道'
when la.branchtype = '6' and la.branchtype2 = '03' then '社保中介渠道'
when la.branchtype = '7' and la.branchtype2 = '01' then '健管渠道'
when la.branchtype = '8' and la.branchtype2 = '02' then '电商渠道'
end
from lbappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName order by la.employdate desc fetch first 1 rows only
) 投保人所在销售渠道,
(select la.groupagentcode from lbappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName order by la.employdate desc fetch first 1 rows only) 集团工号,
(select case when agentstate>='06' then '离职' else '在职' end from lbappnt lc,laagent la where lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName and la.groupagentcode is not null order by la.employdate desc fetch first 1 rows only) 是否在职,
(select
case
when la.branchtype = '1' and la.branchtype2 = '01' then '个险直销渠道'
when la.branchtype = '1' and la.branchtype2 = '02' then '个险中介渠道'
when la.branchtype = '1' and la.branchtype2 = '03' then '个险交叉销售渠道'
when la.branchtype = '1' and la.branchtype2 = '05' then '个险续收销售渠道'
when la.branchtype = '2' and la.branchtype2 = '01' then '团险直销渠道'
when la.branchtype = '2' and la.branchtype2 = '02' then '团险中介渠道'
when la.branchtype = '3' and la.branchtype2 = '01' then '银保渠道'
when la.branchtype = '4' and la.branchtype2 = '01' then '电话销售渠道'
when la.branchtype = '5' and la.branchtype2 = '01' then '互动渠道'
when la.branchtype = '6' and la.branchtype2 = '01' then '社保直销渠道'
when la.branchtype = '6' and la.branchtype2 = '02' then '社保综拓中介渠道'
when la.branchtype = '6' and la.branchtype2 = '03' then '社保中介渠道'
when la.branchtype = '7' and la.branchtype2 = '01' then '健管渠道'
when la.branchtype = '8' and la.branchtype2 = '02' then '电商渠道'
end from lbinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name order by la.employdate desc fetch first 1 rows only
) 被保险人所在销售渠道,
(select groupagentcode from lbinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name order by la.employdate desc fetch first 1 rows only) 集团工号,
(select case when agentstate>='06' then '离职' else '在职' end
from lbinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name and la.groupagentcode is not null order by la.employdate desc fetch first 1 rows only) 是否在职
from lbpol lcp
where lcp.conttype='1' and lcp.appflag in ('1','3')
and lcp.cvalidate>='2025-06-01' and lcp.cvalidate<='2025-06-10' and salechnl IN ('04','13','14','15')
and exists (select 1 from lbappnt lc, laagent la WHERE lc.contno=lcp.contno and lc.appntno=lcp.appntno and la.IDNO = lc.IDNo AND la.NAME = lc.AppntName and la.agentstate<'06'
union select 1 from lbinsured lci,laagent la WHERE lci.contno=lcp.contno and lcp.insuredno=lci.insuredno and la.IDNO = lci.IDNo AND la.NAME = lci.Name and la.agentstate<'06')--la.agentstate<'06'在职
and lcp.edorno not like 'xb%' ; 帮我优化一下db2的sql语句
最新发布