troubleshooting Question

Query and Sum

Avatar of eciabattari
eciabattari asked on
Microsoft SQL Server 2008
3 Comments1 Solution512 ViewsLast Modified:
I am working on developing a query against Microsoft Project Server database.  I want the ability to calculate the amount of hours an individual will be working within a given period of time.  I've developed the code noted below. and it works, for the most part.  

However, as you will see, if a 'resource' has multiple tasks during that week, it will return that resource multiple times.  I was to return only 1 row for each individuals.

Here's what I need:
Resource Name                     Work Sum
V                                                44
W                                                20
M                                                14
B                                                 0

Can someone assist?
SELECT     MSP_EpmAssignment.AssignmentStartDate, MSP_EpmAssignment.AssignmentFinishDate, SUM(MSP_EpmAssignmentByDay_UserView.AssignmentWork) 
                      AS WorkSum, MSP_EpmResource_UserView.ResourceName
FROM         MSP_EpmAssignmentByDay_UserView INNER JOIN
                      MSP_EpmProject_UserView ON MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                      MSP_EpmTask_UserView ON MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID AND 
                      MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID INNER JOIN
                      MSP_EpmAssignment ON MSP_EpmAssignmentByDay_UserView.AssignmentUID = MSP_EpmAssignment.AssignmentUID AND 
                      MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmAssignment.ProjectUID AND 
                      MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmAssignment.TaskUID INNER JOIN
                      MSP_EpmResource_UserView ON MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID
GROUP BY MSP_EpmAssignment.AssignmentStartDate, MSP_EpmAssignment.AssignmentFinishDate, MSP_EpmResource_UserView.ResourceName
HAVING      (MSP_EpmAssignment.AssignmentStartDate >= CONVERT(DATETIME, '2011-08-28 00:00:00', 102)) AND 
                      (MSP_EpmAssignment.AssignmentFinishDate <= CONVERT(DATETIME, '2011-09-04 00:00:00', 102)) AND 
                      (NOT (MSP_EpmResource_UserView.ResourceName IS NULL))
Query.jpg
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros