Unable to find assigned work for a resource for a specfic project only from reporting database

• 21 April 2012 11:31

   

   
  

  0

  Hi All,

  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.

  
  
  
  

  • Diedit oleh George-Admin 21 April 2012 11:37
  • Diedit oleh George-Admin 21 April 2012 11:38
  • Diedit oleh George-Admin 21 April 2012 11:39
  • Diedit oleh George-Admin 21 April 2012 12:41
  •  
  • Balas
  • Kutip