// 다면평가 대상 선정
// 평가조직 기반의 다면평가 대상자 선정
truncate table 역량다면평가
go;
INSERT INTO 역량다면평가 (
피평정자사번
,피평정자
,평정자사번
,평정자
,평정자소속
,평가소속
,랭크
,상태
,다면평가그룹
,평정그룹
,입사일자
,평정자평정그룹
,평정자입사일자
,도민지향,변화주도,현장중심,청렴공정
,리더십지표1,리더십지표2,리더십지표3,리더십지표4,의사소통
,업무추진력,판단력,업무전문성,정보수집,업무성실성
,평점
,OWNER_ID
,OWNER_NAME
,REG_DATETIME
,REG_USER_ID
,REG_USER_NAME
,EDT_DATETIME
,EDT_USER_ID
,EDT_USER_NAME
)
select
trim(a.사번) as 피평정자사번
,trim(a.성명) as 피평정자이름
,trim(b.사번) as 평정자사번
,trim(b.성명) as 평정자이름
,b.조직명
, a.평가조직
, RANK() OVER (PARTITION BY a.평가조직, a.사번 ORDER BY b.사번 DESC) AS 랭크
, '평가전'
,a.다면평가그룹
,a.평정그룹
,a.입사일자
,b.평정그룹
,b.입사일자
,80,80,80,80
,80,80,80,80,80
,80,80,80,80,80
,80
,:userId
,:userName
,:now
,:userId
,:userName
,:now
,:userId
,:userName
from 사원 a
inner join 사원 b on a.평가조직 = b.평가조직 and a.사번 != b.사번
and a.평가조직 not in ('인사부/휴직','-')
and a.평정그룹 in ('1~3급(부점장)', '1~3급(부점장외)', '4급', '5~6급','공무직원')
-- and IFNULL(a.겸직여부,'N') = 'N'
and b.평정그룹 in ('x1~3급(부점장)', '1~3급(부점장외)', '4급', '5~6급','공무직원')
and a.다면평가그룹 not in ('-')
go;
// 본부장급은 하위부서의 부서장을 통한 다면평가대상선정
INSERT INTO 역량다면평가 (
피평정자사번
,피평정자
,평정자사번
,평정자
,평정자소속
,평가소속
,랭크
,상태
,다면평가그룹
,평정그룹
,입사일자
,평정자평정그룹
,평정자입사일자
,도민지향,변화주도,현장중심,청렴공정
,리더십지표1,리더십지표2,리더십지표3,리더십지표4,의사소통
,업무추진력,판단력,업무전문성,정보수집,업무성실성
,평점
,OWNER_ID
,OWNER_NAME
,REG_DATETIME
,REG_USER_ID
,REG_USER_NAME
,EDT_DATETIME
,EDT_USER_ID
,EDT_USER_NAME
)
select
trim(a.사번) as 피평정자사번
,trim(a.성명) as 피평정자이름
,trim(b.사번) as 평정자사번
,trim(b.성명) as 평정자이름
,b.조직명
,a.평가조직
, RANK() OVER (PARTITION BY a.평가조직, a.사번 ORDER BY b.사번 DESC) AS 랭크
, '평가전'
, a.다면평가그룹
, a.평정그룹
, a.입사일자
, b.평정그룹
, b.입사일자
,80,80,80,80
,80,80,80,80,80
,80,80,80,80,80
,80
,:userId
,:userName
,:now
,:userId
,:userName
,:now
,:userId
,:userName
from 사원 a
inner join 사원 b on a.조직명 = b.상위조직명 and a.사번 != b.사번
and a.평가조직 not in ('인사부/휴직','-')
and a.평정그룹 in ('본부장')
and b.평정그룹 in ('1~3급(부점장)')
and a.다면평가그룹 not in ('-')
// 평가결과 수집
// 피평정자기준으로 다면평가 결과수집
truncate table 역량평가결과
go;
INSERT INTO 역량평가결과 (
피평정자사번 ,피평정자,평가소속,다면평가그룹,평정그룹,입사일자
,S01
,S02
,S03
,S04
,S05
,S06
,S07
,S08
,S09
,S10
,S11
,S12
,OWNER_ID
,OWNER_NAME
,REG_DATETIME
,REG_USER_ID
,REG_USER_NAME
,EDT_DATETIME
,EDT_USER_ID
,EDT_USER_NAME
)
select
피평정자사번,피평정자, 평가소속,다면평가그룹,평정그룹,입사일자
, SUM(IF(랭크 = 1, 평점, null)) AS S01
, SUM(IF(랭크 = 2, 평점, null)) AS S02
, SUM(IF(랭크 = 3, 평점, null)) AS S03
, SUM(IF(랭크 = 4, 평점, null)) AS S04
, SUM(IF(랭크 = 5, 평점, null)) AS S05
, SUM(IF(랭크 = 6, 평점, null)) AS S06
, SUM(IF(랭크 = 7, 평점, null)) AS S07
, SUM(IF(랭크 = 8, 평점, null)) AS S08
, SUM(IF(랭크 = 9, 평점, null)) AS S09
, SUM(IF(랭크 = 10, 평점, null)) AS S10
, SUM(IF(랭크 = 11, 평점, null)) AS S11
, SUM(IF(랭크 = 12, 평점, null)) AS S12
,:userId
,:userName
,:now
,:userId
,:userName
,:now
,:userId
,:userName
from (
select
피평정자사번,피평정자, 평가소속,다면평가그룹,평정그룹, 평점,입사일자,
RANK() OVER (PARTITION BY 평가소속,피평정자사번 ORDER BY 평정자평정그룹, 평정자입사일자, 평정자사번 ) AS 랭크
from 역량다면평가
) a
group by 피평정자사번,피평정자, 평가소속,다면평가그룹,평정그룹,입사일자
go;
// 최대 최소값을 제외한 평균점수 산정
update 역량평가결과 a
inner join (
WITH RankedScores AS (
SELECT
피평정자사번,
피평정자,
평가소속,
다면평가그룹,
평정그룹,
평점,
RANK() OVER (PARTITION BY 평가소속, 피평정자사번 ORDER BY 평점 DESC, 평정자사번) AS RankDesc,
RANK() OVER (PARTITION BY 평가소속, 피평정자사번 ORDER BY 평점 ASC , 평정자사번) AS RankAsc
FROM 역량다면평가
),
FilteredScores AS (
SELECT
피평정자사번,
피평정자,
평가소속,
다면평가그룹,
평정그룹,
평점
FROM RankedScores
WHERE RankDesc > 1 AND RankAsc > 1 -- 최고, 최저 제외
)
SELECT
피평정자사번,
평가소속,
AVG(평점) AS 평균평점
FROM FilteredScores
GROUP BY
피평정자사번,
평가소속
) b on a.평가소속 = b.평가소속 and a.피평정자사번 = b.피평정자사번
set a.역량점수 = b.평균평점