105 lines
2.3 KiB
SQL
105 lines
2.3 KiB
SQL
SELECT *
|
|
FROM reward_gainers
|
|
FOR UPDATE;
|
|
|
|
SELECT b.reward_name,
|
|
a.*
|
|
FROM reward_gainers a,
|
|
reward_projects b
|
|
WHERE a.reward_index = b.reward_index;
|
|
|
|
--坐席工号
|
|
SELECT DISTINCT s.saler_code,
|
|
s.saler_name,
|
|
t.team_code,
|
|
t.team,
|
|
bm.department_code,
|
|
bm.department_name
|
|
FROM tele_saler s,
|
|
tele_saler_team t,
|
|
idst0.bm_t bm
|
|
WHERE s.team_code = t.team_code
|
|
AND t.department_code = bm.department_code
|
|
AND s.saler_code = '61136';
|
|
|
|
SELECT *
|
|
FROM tele_saler_team t
|
|
WHERE t.team_code = '107077'
|
|
|
|
SELECT *
|
|
FROM 坐席车非每日保费
|
|
ORDER BY 坐席名称,
|
|
签单日期;
|
|
|
|
|
|
SELECT 部门,
|
|
SUM(已续保累计) 已续保,
|
|
SUM(到期数) 到期保单,
|
|
round((SUM(已续保累计) / SUM(到期数)) * 100,
|
|
0) || '%' 续保率
|
|
FROM 坐席续保统计
|
|
GROUP BY 部门;
|
|
|
|
--车非个人每月业绩
|
|
SELECT 月份,
|
|
部门代码,
|
|
坐席名称,
|
|
SUM(车险个人客户保费),
|
|
SUM(车非融合保费),
|
|
round(SUM(车非融合保费) / SUM(车险个人客户保费) * 100,
|
|
2) 车非渗透率
|
|
FROM 坐席车非每日保费
|
|
GROUP BY 月份,
|
|
部门代码,
|
|
坐席名称
|
|
HAVING 月份 = '10月'
|
|
ORDER BY 部门代码,
|
|
车非渗透率 DESC;
|
|
|
|
SELECT 坐席名称,
|
|
round(SUM(车非融合保费) / SUM(车险个人客户保费) * 100,
|
|
2) 车非渗透率
|
|
FROM 坐席车非每日保费
|
|
WHERE 部门代码 = 'QDI'
|
|
AND 月份 = '10'
|
|
AND 年份 = '2023'
|
|
GROUP BY 坐席名称
|
|
ORDER BY 车非渗透率 DESC;
|
|
|
|
--续保率
|
|
SELECT 坐席名称,
|
|
round(nvl(SUM(已续保累计) / SUM(到期数) * 100,
|
|
0),
|
|
2) 续保率
|
|
FROM 坐席续保统计 xb
|
|
WHERE xb.部门 = '续保业务部'
|
|
GROUP BY 坐席名称
|
|
ORDER BY 坐席名称 DESC;
|
|
-------------------------------------
|
|
SELECT zx.saler_name,
|
|
bm.department_name
|
|
FROM tele_saler zx,
|
|
tele_saler_team team,
|
|
idst0.bm_t bm
|
|
WHERE zx.saler_name = '张楚沂'
|
|
AND zx.team_code = team.team_code
|
|
AND team.department_code = bm.department_code;
|
|
----------------------------------------
|
|
/*
|
|
truncate table BI机构渗透率跟踪表;
|
|
*/
|
|
|
|
SELECT *
|
|
FROM BI电销坐席车非渗透率跟踪表 t
|
|
WHERE t.经办 = '张楚沂';
|
|
|
|
SELECT *
|
|
FROM BI电销坐席续保率跟踪表
|
|
WHERE 责任人 = '张楚沂';
|
|
|
|
SELECT *
|
|
FROM BI机构渗透率跟踪表;
|
|
|
|
SELECT *
|
|
FROM BI机构当月个车续保率跟踪表;
|