需求
所有部门汇总的结果的打分。大部分指标是根据部门汇总(SUM或AVG)结果打分。
但面谈率和前10%加班平均小时数俩指标，不是根据所有部门(TEAM)汇总(SUM或AVG)结果打分。而是需要根据每个部门的
数据做排序或累加后的结果打分
面谈率计算方式：TEAM1中季度1月份，面谈人数比例目标是33% -》 目标面谈人数 = 部门人数*33%
实际面谈人数 = 10
TEAM1中季度2月份，面谈人数比例目标是60% -》 目标面谈人数 = 部门人数*60%
实际面谈人数 = 2 + 10（1月份实际面谈人数）
TEAM1中季度3月份，面谈人数比例目标是100% -》目标面谈人数 = 部门人数*100%
实际面谈人数 = 1 + 12（2月份实际面谈人数）
这里部门人数是按季度统计每个季度部门的总人数(且不算领导，在之前存储过程中部门人数已确定，CVS_DEPT_PAX_CNT表示），因为每季度部门人数或部门关系都可能变更
计算每月实际面谈率 = 实际面谈人数 / CAST(目标面谈人数 AS NUMERIC(19,2)) * 100
注意 这里是把整数转换成小数保证不用整除计算。要求是小数
具体SQL如下
/*每个group的统计*/
DECLARE @CVS_SING_STAT AS TABLE
(
TEAM_CD VARCHAR(20)
,GRP_CD VARCHAR(20)
,CHECK_RADIO numeric(19,2)
)
INSERT INTO @CVS_SING_STAT
SELECT T.TEAM_CD,T.DEPT_CD GRP_CD,T.CHECK_RADIO
FROM /*如下内层查询是在SQL SERVER 2008上做每行的累积运算*/
SELECT SUBSTRING(BASE.DHSTC_CD,0,5) DH_YEAR ,BASE.STC_MONTH ,SUBSTRING(BASE.DHSTC_CD,5,8) DH_MONTH
,BASE.TEAM_CD,BASE.DEPT_CD,BASE.H09_CNT,BASE.CVS_DEPT_PAX_CNT
,SUM(ROLL.H09_CNT) ACC
,SUM(ROLL.H09_CNT)/BASE.CVS_DEPT_PAX_CNT CVS_RAT
--,33.0/100.0
--,CAST(33.0/100.0 AS NUMERIC(19,2))
--,SUM(ROLL.H09_CNT)/(BASE.CVS_DEPT_PAX_CNT*(33.0/100.0)) 就是实际面谈人数/目标面谈人数
--,CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_DEPT_PAX_CNT*(33.0/100.0)) AS NUMERIC(19,2)) FLOATTEST
,CASE WHEN BASE.STC_MONTH = '01' THEN CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_DEPT_PAX_CNT*(33.0/100.0)) AS NUMERIC(19,2))
WHEN BASE.STC_MONTH = '02' THEN CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_DEPT_PAX_CNT*(66.0/100.0)) AS NUMERIC(19,2))
WHEN BASE.STC_MONTH = '03' THEN CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_DEPT_PAX_CNT*(100.0/100.0)) AS NUMERIC(19,2))
END CHECK_RADIO
FROM T_DM_DHSTC_DEPT BASE
,T_DM_DHSTC_DEPT ROLL
WHERE SUBSTRING(BASE.DHSTC_CD,0,5) = SUBSTRING(ROLL.DHSTC_CD,0,5)--YEAR
AND BASE.STC_QUATR = ROLL.STC_QUATR
AND BASE.STC_MONTH >= ROLL.STC_MONTH
AND BASE.TEAM_CD = ROLL.TEAM_CD
AND BASE.DEPT_CD = ROLL.DEPT_CD
-- AND BASE.DEPT_CD = 'EA190086'
GROUP BY SUBSTRING(BASE.DHSTC_CD,0,5),BASE.TEAM_CD,BASE.DEPT_CD,SUBSTRING(BASE.DHSTC_CD,5,8),BASE.STC_MONTH,BASE.H09_CNT,BASE.CVS_DEPT_PAX_CNT
)T
WHERE T.DH_YEAR = @YEAR AND T.DH_MONTH = @MONTH
/*每个TEAM的统计*/
DECLARE @CVS_TEAM_STAT AS TABLE
(
TEAM_CD VARCHAR(20)
,GRP_CD VARCHAR(20)
,CHECK_RADIO numeric(19,2)
)
DECLARE @CVS_TEAM_BASE AS TABLE
(
DH_YEAR VARCHAR(20)
,STC_QUATR VARCHAR(20)
,STC_MONTH VARCHAR(20)
,DH_MONTH VARCHAR(20)
,TEAM_CD VARCHAR(20)
,DEPT_CD VARCHAR(20)
,CVS_GRP_PAX_CNT numeric(19,2)
,H09_CNT numeric(19,2)
)
INSERT INTO @CVS_TEAM_BASE
SELECT T1.DH_YEAR,T2.STC_QUATR,T1.DH_MONTH,T2.STC_MONTH,T2.TEAM_CD,'-' GRP_CD
,SUM(CVS_DEPT_PAX_CNT) CVS_GRP_PAX_CNT
,SUM(T2.H09_CNT) ACC_H09_CNT
FROM T_DM_DHSTC T1
,T_DM_DHSTC_DEPT T2
WHERE
--and TEAM_CD = 'EA190001'
T1.DHSTC_CD = T2.DHSTC_CD
GROUP BY T1.DH_YEAR,T2.STC_QUATR,T1.DH_MONTH,T2.STC_MONTH
,T2.TEAM_CD
INSERT INTO @CVS_TEAM_STAT
SELECT T.TEAM_CD,'-' GRP_CD,T.CHECK_RADIO
FROM(
SELECT BASE.DH_YEAR,MAX(BASE.STC_QUATR) STC_QUATR,BASE.DH_MONTH,BASE.STC_MONTH,BASE.TEAM_CD,BASE.H09_CNT
,SUM(ROLL.H09_CNT) ACC
,BASE.CVS_GRP_PAX_CNT
,SUM(ROLL.H09_CNT)/BASE.CVS_GRP_PAX_CNT CVS_RAT
,CASE WHEN BASE.STC_MONTH = '01' THEN CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_GRP_PAX_CNT*(33.0/100.0)) AS NUMERIC(19,2))
WHEN BASE.STC_MONTH = '02' THEN CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_GRP_PAX_CNT*(66.0/100.0)) AS NUMERIC(19,2))
WHEN BASE.STC_MONTH = '03' THEN CAST(SUM(ROLL.H09_CNT)/(BASE.CVS_GRP_PAX_CNT*(100.0/100.0)) AS NUMERIC(19,2))
END CHECK_RADIO
FROM @CVS_TEAM_BASE BASE
,@CVS_TEAM_BASE ROLL
WHERE BASE.DH_YEAR = ROLL.DH_YEAR
--AND BASE.DH_MONTH = ROLL.DH_MONTH
AND BASE.STC_QUATR = ROLL.STC_QUATR
AND BASE.DH_MONTH >= ROLL.DH_MONTH
AND BASE.TEAM_CD = ROLL.TEAM_CD
GROUP BY BASE.DH_YEAR,BASE.DH_MONTH,BASE.STC_MONTH,BASE.TEAM_CD,BASE.H09_CNT,BASE.CVS_GRP_PAX_CNT
)T
WHERE T.DH_YEAR = @YEAR AND T.DH_MONTH = @MONTH
