Please Help Me, Very Emergency(query Problem)

[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='Q uality' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as Quality,
CASE WHEN s.tran_param='E fficiency & Productivity' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as Efficiency,
CASE WHEN s.tran_param='S kill Based' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as Skill
from tb_tran_storera ting s, tb_mast_employe e e, tb_master_proje ct p, tb_master_title t where s.tran_empid=e. M_EMPL_ID_PK and s.tran_proj=p.P ROJ_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_employe e 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

[ScarletPimpernal]

Hi Barathi,

Can u provide the table datas?




Thanks,
Scarlet

[barrathi]

hai,

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

above is table data

iam using case when query


but i need following output(no duplicates)

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

538 peter 4 3 4

[ScarletPimpernal]

Hi Bharati,


May be this query work for you... Try this

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='Q uality' THEN
ROUND((SUM(s.tr an_revscore)/(s.tran_weighta ge/100)))
END AS Quality,
CASE
WHEN s.tran_param='E fficiency & Productivity' THEN
ROUND((SUM(s.tr an_revscore)/(s.tran_weighta ge/100)))
END as Efficiency,
CASE
WHEN s.tran_param='S kill Based' THEN
ROUND((SUM(s.tr an_revscore)/(s.tran_weighta ge/100)))
END as Skill
FROM
tb_tran_storera ting s,
tb_mast_employe e e,
tb_master_proje ct p,
tb_master_title t
WHERE
s.tran_empid=e. M_EMPL_ID_PK AND s.tran_proj=p.P ROJ_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_employe e WHERE M_EMPL_PROJECT ='1')
GROUP BY
tran_empid;


Thanks,
Scarlet

[barrathi]

Hi Bharati,


May be this query work for you... Try this

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='Q uality' THEN
ROUND((SUM(s.tr an_revscore)/(s.tran_weighta ge/100)))
END AS Quality,
CASE
WHEN s.tran_param='E fficiency & Productivity' THEN
ROUND((SUM(s.tr an_revscore)/(s.tran_weighta ge/100)))
END as Efficiency,
CASE
WHEN s.tran_param='S kill Based' THEN
ROUND((SUM(s.tr an_revscore)/(s.tran_weighta ge/100)))
END as Skill
FROM
tb_tran_storera ting s,
tb_mast_employe e e,
tb_master_proje ct p,
tb_master_title t
WHERE
s.tran_empid=e. M_EMPL_ID_PK AND s.tran_proj=p.P ROJ_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_employe e WHERE M_EMPL_PROJECT ='1')
GROUP BY
tran_empid;


Thanks,
Scarlet

THANKS A LOT SCARLET,
BUT STILL Iam not getting any solution.

anyway thanks