October 1, 2005 at 9:05 pm
I need to take the counts of distinct resources per project.
Expected output:
Project Name Resource Headcount
___________________________________
ProjectA 0
Project B Consolidation 5
Project C 2
***********************************************
SQL 1:
select
RES_NAME as Resource,
PROJ_NAME as Project,
a.Task_name
from
MSP_WEB_PROJECTS p,
MSP_WEB_ASSIGNMENTS a,
MSP_WEB_RESOURCES r
where
r.WRES_ID = a.WRES_ID
and a.WPROJ_ID = p.WPROJ_ID
and p.WPROJ_ID <> 1
order by PROJ_NAME, RES_NAME
**********************************************
OUTPUT
RES_NAME,PROJ_NAME, TASK_NAME
Chon Peter - wzmr1j BPA test1 Evolve Assignement Task
Hanson Sean - bzlj08 BPA test1 Build Task
Mccoppin Ray - dz776s BPA test1 Build Task
Mccoppin Ray - dz776s BPA test1 Design Task
Hanson Sean - bzlj08 Evolve 1 Task a
Hanson Sean - bzlj08 Evolve 1 Evolve Summary
Hanson Sean - bzlj08 Evolve 1 Task Summary
Hanson Sean - bzlj08 Evolve 1 Evolve
Hanson Sean - bzlj08 Evolve 1 Task c
Hanson Sean - bzlj08 Evolve 1 Evolve Summary (assignment)
Mccoppin Ray - dz776s Evolve 1 Evolve Summary (assignment)
Mccoppin Ray - dz776s Evolve 1 Evolve Summary
Mccoppin Ray - dz776s Evolve 1 Task Summary
Mccoppin Ray - dz776s Evolve 1 Task c
Mccoppin Ray - dz776s Evolve 1 Task a
Mccoppin Ray - dz776s Evolve 1 Task b
Mccoppin Ray - dz776s Evolve 1 Evolve
--------------------------------------------------------------
SQL2: select
PROJ_NAME as Project,
count (distinct RES_NAME) as RES_NAME_COUNT
from
MSP_WEB_PROJECTS p,
MSP_WEB_ASSIGNMENTS a,
MSP_WEB_RESOURCES r
where
r.WRES_ID = a.WRES_ID
and a.WPROJ_ID = p.WPROJ_ID
and p.WPROJ_ID <> 1
Group by PROJ_NAME, RES_NAME
order by PROJ_NAME, RES_NAME
***********************************************
OUTPUT
BPA test1 1
BPA test1 1
BPA test1 1
Evolve 1 1
Evolve 1 1
*********************************************
What can I do to sum the total distinct resources per project?
Thanks
October 2, 2005 at 10:42 pm
You're on the right track. However, change your GROUP BY and ORDER BY clauses by removing the RES_NAME column.
With the inner joins, you will only get projects that have a resource assigned. If you want to include projects that do not have an assigned resource, you need to use outer joins.
Hope this helps,
Scott Thornburg
October 3, 2005 at 9:03 am
That works well. Thank you, Scott.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply