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
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))