Resource Assigned Work SQL Query for a Project Server Report

Below is a query which brings back assigned work on a time basis. This maybe useful for some people (maybe not as useful as the resource capacity one I just posted though);

SELECT
MSP_EpmResource_UserView.ResourceName,
MSP_EpmProject_UserView.ProjectName,
Year(MSP_EpmAssignmentByDay_UserView.TimeByDay) AS Year,
MONTH(MSP_EpmAssignmentByDay_UserView.TimeByDay) AS Month,
Day(MSP_EpmAssignmentByDay_UserView.TimeByDay) AS Day,
SUM(MSP_EpmAssignmentByDay_UserView.AssignmentWork) AS AssignmentWork
FROM
MSP_EpmResource_UserView
INNER JOIN MSP_EpmTask_UserView
INNER JOIN MSP_EpmProject_UserView ON
MSP_EpmTask_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID
INNER JOIN
MSP_EpmAssignment ON MSP_EpmTask_UserView.ProjectUID =
MSP_EpmAssignment.ProjectUID AND MSP_EpmTask_UserView.TaskUID =
MSP_EpmAssignment.TaskUID ON MSP_EpmResource_UserView.ResourceUID =
MSP_EpmAssignment.ResourceUID
INNER JOIN
MSP_EpmAssignmentByDay_UserView ON
MSP_EpmAssignment.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID
/*WHERE
(MSP_EpmAssignmentByDay_UserView.TimeByDay BETWEEN ’01/01/2010′ AND ’12/31/2015′)*/
GROUP BY
MSP_EpmResource_UserView.ResourceName,
Year(MSP_EpmAssignmentByDay_UserView.TimeByDay),
MONTH(MSP_EpmAssignmentByDay_UserView.TimeByDay),
Day(MSP_EpmAssignmentByDay_UserView.TimeByDay),
MSP_EpmProject_UserView.ProjectName

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s