Problem with qery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with qery

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2008-11-06 : 07:14:53

Hi,
I have a query which gives details of a project like project_name,
manager_name and count of employees based on their skills (like total of employees working on JAVA,DB,DOTNET etc).
Now i want one more column to be added which gives the count of trainees(TSE) in tht proj. i am not able to run the query if i add this column. Please advice.

The query is as below:
select p.prj_project_name,pm.emp_name PM,pl.emp_name PL,gm.emp_name GM,
SUM(CONVERT(INT,rra.RRA_BILLABLE)) BILLABLE,count(rra.emp_seq_no) total,
sum(case when sb.sbd_item_desc = 'Microsoft Technology' then 1 else 0 end) as MSTech,
case when rra.rra_proj_member_status = 'TSE' then count(rra.emp_seq_no) else 0 end as TSEfrom rpmg_resource_allocations rra
inner join project p on p.prj_seq_no =rra.prj_seq_no
inner join sys_business_code_detail sb1 on sb1.sbd_item_code = p.prj_status_item_code
and sb1.sbm_type_code = 'SZ' and sb1.sbd_item_desc not in ('Completed','Scrapped')
inner join employee e on e.emp_seq_no = rra.emp_seq_no and e.emp_status_item_code = 1 and rra.rra_status = 1
inner join employee pm on pm.emp_seq_no = p.prj_pm_seq_no
inner join employee pl on pl.emp_seq_no = p.prj_pl_seq_no
inner join employee gm on gm.emp_seq_no = p.prj_gm_seq_no
inner join employee_detail ed on ed.emp_seq_no = e.emp_seq_no
inner join sys_business_code_detail sb on sb.sbd_item_code = ed.eed_primaryskills and sb.sbm_type_code = 'PK'
group by p.prj_project_name,pm.emp_name ,pl.emp_name ,gm.emp_name,
rra.rra_proj_member_status

the highlighted statement is to be corrected.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 07:26:41

try like belwo

count(case when rra.rra_proj_member_status = 'TSE' then rra.emp_seq_no else null end) as TSEfrom