select
to_id, to_nm
from
(
/*피평가자*/
select
emp_num to_id, emp_nam to_nm
from
(
select
a.emp_num, a.emp_nam, isnull(prgs_stts, '00') prgs_stts
from
(
select
a.org_cd, a.org_nam, a.emp_num, a.emp_nam,b.vlt_grop_cd,
( select prgs_stts
from bdap042t
where vlt_year = '2005'
and sequ_cd = '1'
and vlt_clss_cd = '1'
and nonappraiser = a.emp_num
) prgs_stts,
( select ablt_vlt_use_flg
from bdap038t
where vlt_year = '2005'
and vlt_grop_cd = b.vlt_grop_cd
) use_flg
from hmpa001m a, bdap036t b
where b.vlt_year = '2005'
and a.lineal_cd = b.lineal_cd
and a.jpstn_cd = b.jpstn_cd
and a.jtit_cd = b.jtit_cd
) a
where use_flg = '1'
and emp_num not in ( select nonappraiser
from bdap102t
where vlt_year = '2005'
and sequ_cd = '1'
and exld_clss_cd != ''
)
) a
where prgs_stts < '07'
and prgs_stts in ('00', '01', '02') //평가전, 평가중
/*목표완료, 1차목표협의, 2차목표합의*/
select
to_id, emp_nam to_nm
from
(
select
case when prgs_stts = '03' then /*목표완료*/
case when confi_seqc1_apps != '' then confi_seqc1_apps
else case when confi_seqc2_apps != '' then confi_seqc2_apps
else appraiser
end
end
when prgs_stts = '04' then /*1차목표협의*/
confi_seqc2_apps
when prgs_stts = '05' then /*2차목표협의*/
appraiser
end to_id
from
(
select
prgs_stts, confi_seqc1_apps, confi_seqc2_apps, appraiser
from
(
select
a.emp_num, a.emp_nam, isnull(prgs_stts, '00') prgs_stts
from
(
select
a.org_cd, a.org_nam, a.emp_num, a.emp_nam,b.vlt_grop_cd,
( select prgs_stts
from bdap042t
where vlt_year = '2005'
and sequ_cd = '1'
and vlt_clss_cd = '1'
and nonappraiser = a.emp_num
) prgs_stts,
( select ablt_vlt_use_flg
from bdap038t
where vlt_year = '2005'
and vlt_grop_cd = b.vlt_grop_cd
) use_flg
from hmpa001m a, bdap036t b
where b.vlt_year = '2005'
and a.lineal_cd = b.lineal_cd
and a.jpstn_cd = b.jpstn_cd
and a.jtit_cd = b.jtit_cd
) a
where use_flg = '1'
and emp_num not in ( select nonappraiser
from bdap102t
where vlt_year = '2005'
and sequ_cd = '1'
and exld_clss_cd != ''
)
) a, bdap012t b
where a.emp_num = b.nonappraiser
and a.prgs_stts < '07'
and a.prgs_stts in ('03', '04', '05') //목표완료, 1차목표협의, 2차목표협의
and b.vlt_year = '2006'
and b.sequ_cd = '1'
and b.appraiser is not null
) a
) a, hmpa001m b
where a.to_id = b.emp_num
) a
group by to_id, to_nm

ㅋㅋ 넘 길당..