query problem (case when)

[barrathi]

Hi all

i need one query help!

for reporting purpose i wrote one query - follows

select s.tran_empid as EmpId, e.M_EMPL_NAME as EmpName, p.PROJ_NAME as Project, t.TITL_NAME as Title,CASE WHEN s.tran_param='Project Level Key Initiatives' THEN round((sum(s.tran_revscore)/(s.tran_weightage/100))) END as PLKI,CASE WHEN s.tran_param='Quality' THEN round((sum(s.tran_revscore)/(s.tran_weightage/100))) END as Quality,CASE WHEN s.tran_param='Efficiency & Productivity' THEN round((sum(s.tran_revscore)/(s.tran_weightage/100))) END as Efficiency,CASE WHEN s.tran_param='Schedule Adherence' THEN round((sum(s.tran_revscore)/(s.tran_weightage/100))) END as Schedule,CASE WHEN s.tran_param='Skill Based' THEN round((sum(s.tran_revscore)/(s.tran_weightage/100))) END as Skill,CASE WHEN s.tran_param='Competency' THEN round((sum(s.tran_revscore)/(s.tran_weightage/100))) END as Competency,CASE WHEN s.tran_param='People Development' THEN round((sum(s.tran_revscore)/(s.tran_weightage/100))) END as Developement from tb_tran_storerating s, tb_mast_employee e, tb_master_project p, tb_master_title t where s.tran_empid=e.M_EMPL_ID_PK and s.tran_proj=p.PROJ_ID_PK and s.tran_title=t.TITL_ID_PK and tran_month='3' and tran_year='2007' and tran_empid in (select M_EMPL_ID_PK from tb_mast_employee where M_EMPL_PROJECT ='1') group by tran_empid, tran_param

it shows

output:
---------
empno empname quality plki ed
419 Vineeth null 3 null
419 Vineeth null null 3
419 Vineeth 4 null null
538 Peter 4 null null
538 Peter nulll 3 null
538 Peter null null 4


but i need following output

output:
---------
empno empname quality plki ed
419 vineeth 4 3 3
538 peter 4 3 4


please help meeeeeeeeeeeee..


thanks in advance

barrathi

[barrathi]

HAI ALL,

for reporting purpose i wrote one query - follows

select s.tran_empid as EmpId, e.M_EMPL_NAME as EmpName, p.PROJ_NAME as Project, t.TITL_NAME as Title,
CASE WHEN s.tran_param='Quality' THEN round((sum(s.tran_revscore)/(s.tran_weightage/100))) END as Quality,
CASE WHEN s.tran_param='Efficiency & Productivity' THEN round((sum(s.tran_revscore)/(s.tran_weightage/100))) END as Efficiency,
CASE WHEN s.tran_param='Skill Based' THEN round((sum(s.tran_revscore)/(s.tran_weightage/100))) END as Skill
from tb_tran_storerating s, tb_mast_employee e, tb_master_project p, tb_master_title t where s.tran_empid=e.M_EMPL_ID_PK and s.tran_proj=p.PROJ_ID_PK and s.tran_title=t.TITL_ID_PK and tran_month='3' and tran_year='2007' and tran_empid in (select M_EMPL_ID_PK from tb_mast_employee where M_EMPL_PROJECT ='1') group by tran_empid, tran_param
it shows

output:
---------
empno empname quality efficiency skill
419 Vineeth null 3 null
419 Vineeth null null 3
419 Vineeth 4 null null
538 Peter 4 null null
538 Peter nulll 3 null
538 Peter null null 4


but i need following output

output:
---------
empno empname quality efficency skill
419 vineeth 4 3 3
538 peter 4 3 4

HELP ME PLEASE..

thanks
barrathi