21 April 2012 11:31
For one of my SQL report, I want to fetch Assignment work for all resources in a particular project team.
For the same I am using below query :
DECLARE@YEAR AS VARCHAR(4)
DECLARE@PROJECTS AS VARCHAR(250)
SET@YEAR = '2012'
SET@PROJECTS = 'Test Project'
SELECT SUM(MSP_EpmAssignmentByDay.AssignmentWork) AS QTR1_Work, MSP_EpmProject.ProjectName AS QTR1_ProjName, MSP_EpmResource.ResourceName
FROM MSP_EpmAssignmentByDay INNER JOIN
MSP_EpmAssignment ON MSP_EpmAssignmentByDay.ProjectUID = MSP_EpmAssignment.ProjectUID INNER JOIN
MSP_EpmProject ON MSP_EpmAssignmentByDay.ProjectUID = MSP_EpmProject.ProjectUID AND
MSP_EpmAssignment.ProjectUID = MSP_EpmProject.ProjectUID INNER JOIN
MSP_EpmResource ON MSP_EpmAssignment.ResourceUID = MSP_EpmResource.ResourceUID AND
MSP_EpmAssignment.ResourceOwnerUID = MSP_EpmResource.ResourceUID
WHERE (MSP_EpmAssignmentByDay.TimeByDay >= CAST('01/01/' + @Year AS datetime)) AND
(MSP_EpmAssignmentByDay.TimeByDay <= CAST('03/31/' + @Year AS datetime)) AND
(dbo.MSP_EpmProject.ProjectName IN (@Projects))
GROUP BY PROJECTNAME,RESOURCENAME
But above query is giving me some unexpected result means as a sum of assignment work is giving me all assigned work for a particular resource in that partcular quarter, not specific to that project which is defined in WHERE clause. Please let me know WHAT AND WHERE I am doing wrong.
21 April 2012 15:56Moderator
This query works for me for 2011 and a project called 'asdf'
Select MSP_EpmProject_UserView.ProjectName, MSP_EpmResource_UserView.ResourceName, Sum(MSP_EpmAssignmentByDay_UserView.AssignmentWork) As 'Work'
From MSP_EpmProject_UserView Inner Join
MSP_EpmAssignment_UserView On MSP_EpmProject_UserView.ProjectUID = MSP_EpmAssignment_UserView.ProjectUID Inner Join
MSP_EpmResource_UserView On MSP_EpmResource_UserView.ResourceUID = MSP_EpmAssignment_UserView.ResourceUID Inner Join
MSP_EpmAssignmentByDay_UserView On MSP_EpmAssignment_UserView.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID Inner Join
MSP_TimeByDay_OlapView On MSP_EpmAssignmentByDay_UserView.TimeByDay = MSP_TimeByDay_OlapView.TimeByDay
Group By MSP_EpmProject_UserView.ProjectName, MSP_EpmResource_UserView.ResourceName, MSP_TimeByDay_OlapView.CalendarMemberNameYear
Having MSP_EpmProject_UserView.ProjectName = 'asdf' And MSP_TimeByDay_OlapView.CalendarMemberNameYear = 2011