none
Add Unassign work to a SQL query built by Amit Khare RRS feed

  • Question

  • Hello,

    For a while I was trying to built a SQL query that give's me the resource capacity Vs remaming work for each resource in our organisation. I was realy close to the goal but I found that Amit already did it.

    So I only modify his query to fit my need and it work pretty go! Thanks Amit. Now my only problem is that some is that some project manager does not assign all tasks. I try now find theunassigned task per day.

    This way I can see the total remaining work assigned or not ...

    Someone have a clue?

    The super query:

     SELECT 
                   (SELECT ResourceName from MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID) as [ResourceName], 
                   MSP_EpmResourceByDay_UserView.ResourceUID as [ResourceUID], 
                   ISNULL(AssignmentTable.AllocatedCapacity,0) as [Travail+ResourcePlan], 
    				ISNULL(AssignmentTable.AssignmentType,564) AS [ASSIGMENTTYPE],
                   ISNULL(assignmenttable.RW,0) AS TravailRestant,
                   MSP_EpmResourceByDay_UserView.Capacity as [ResourceCapacity], 
                   DATENAME(month, MSP_EpmResourceByDay_UserView.TimeByDay) as [Month], 
                   YEAR(MSP_EpmResourceByDay_UserView.TimeByDay) as [Year], 
                   --DATENAME(week,MSP_EpmResourceByDay_UserView.TimeByDay) AS [Week],
                   CONVERT(VARCHAR(20),DATEADD(dd, -(DATEPART(dw, MSP_EpmResourceByDay_UserView.TimeByDay)-1), MSP_EpmResourceByDay_UserView.TimeByDay),102) AS FirstDayWeek,
                   (SELECT [RBS] from MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID) as [RBS] 
            
    FROM 
                   dbo.MSP_EpmResourceByDay_UserView 
                   
                  
    LEFT OUTER JOIN 
                   ( 
                    SELECT 
                       MSP_EpmAssignment_UserView.ResourceUID,  
                       MSP_EpmAssignment_UserView.AssignmentType,
                       -- IDENTIFICATION DU TRAVAIL RESTANT
                       SUM(MSP_EpmAssignmentByDay_UserView.AssignmentCombinedWork) as [AllocatedCapacity],
                       SUM(MSP_EpmAssignmentByDay_UserView.AssignmentRemainingWork) AS RW, 
                       --##################################
                       MSP_EpmAssignmentByDay_UserView.TimeByDay 
                    FROM 
                       dbo.MSP_EpmAssignment_UserView 
                    INNER JOIN 
                       MSP_EpmAssignmentByDay_UserView 
                          ON MSP_EpmAssignment_UserView.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID 
                             AND MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmAssignmentByDay_UserView.ProjectUID 
                             AND MSP_EpmAssignment_UserView.TaskUID = MSP_EpmAssignmentByDay_UserView.TaskUID 
                    WHERE 
                       MSP_EpmAssignmentByDay_UserView.TimeByDay BETWEEN  
                                                                 CONVERT(DATETIME, DATEADD(month, - 1, CURRENT_TIMESTAMP), 102) 
                                                                 AND CONVERT(DATETIME, DATEADD(month, 13, CURRENT_TIMESTAMP), 102) 
                    GROUP BY 
                       MSP_EpmAssignment_UserView.ResourceUID, 
                       MSP_EpmAssignmentByDay_UserView.TimeByDay,
                       MSP_EpmAssignment_UserView.AssignmentType
                    ) AS AssignmentTable 
                       ON AssignmentTable.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID 
                          AND AssignmentTable.TimeByDay = MSP_EpmResourceByDay_UserView.TimeByDay 
    
    INNER JOIN MSP_EpmResource_UserView  EpmRess_UV ON MSP_EpmResourceByDay_UserView.ResourceUID  = EpmRess_UV.ResourceUID 
    			AND EpmRess_UV.ResourceIsGeneric = 0 
    			AND RIGHT(EpmRess_UV.RBS,3)='MBR' 
    			AND EpmRess_UV.ResourceIsActive = 1
    			AND LEFT(EpmRess_UV.ResourceName,5) <> 'TECH_'
                           
                     
    WHERE 
                  (MSP_EpmResourceByDay_UserView.TimeByDay > CONVERT(DATETIME, DATEADD(month, - 1, CURRENT_TIMESTAMP), 102)) 
                  AND (MSP_EpmResourceByDay_UserView.TimeByDay < CONVERT(DATETIME, DATEADD(month, 13, CURRENT_TIMESTAMP), 102)) 
                  
    ORDER BY ResourceName,RBS
    


    PWAdmin

    Friday, June 7, 2013 7:41 PM

Answers

  • Hello,

    If I understand you correctly you want to see tasks with no assignments? If so here is a code snippet:

    --Returns tasks with no assignments
    select		P.ProjectName
    		,	T.TaskName
    		,	T.TaskDuration
    		,	T.TaskWork
    from		dbo.MSP_EpmTask_UserView T
    Inner Join	MSP_EpmProject_UserView P
    ON			P.ProjectUID = T.ProjectUID
    where		T.TaskIsProjectSummary = 0 
    			and not exists 
    			(select * 
    			from 	dbo.MSP_EpmAssignment_UserView A 
    			where A.TaskUID = T.TaskUID)

    Hope that helps

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Tuesday, June 11, 2013 11:36 AM
    Moderator

All replies

  • Hello,

    If I understand you correctly you want to see tasks with no assignments? If so here is a code snippet:

    --Returns tasks with no assignments
    select		P.ProjectName
    		,	T.TaskName
    		,	T.TaskDuration
    		,	T.TaskWork
    from		dbo.MSP_EpmTask_UserView T
    Inner Join	MSP_EpmProject_UserView P
    ON			P.ProjectUID = T.ProjectUID
    where		T.TaskIsProjectSummary = 0 
    			and not exists 
    			(select * 
    			from 	dbo.MSP_EpmAssignment_UserView A 
    			where A.TaskUID = T.TaskUID)

    Hope that helps

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Tuesday, June 11, 2013 11:36 AM
    Moderator
  • I change it to find the work that is not #assign. I also want to check it by week.

    select P.[Type projet (RSDE,SFRD, etc.)] , CONVERT(VARCHAR(20),DATEADD(dd, -(DATEPART(dw, TimeByDay)-1), TimeByDay),102) AS FirstDayWeek , ROUND(AU.AssignmentRemainingWork,3) AS roundAssignmentRemainingWork , P.[Directeur / VP projet] , P.[Code de projet] , t.TaskIndex --, T.TaskName --, T.TaskWork --, T.TaskRemainingWork , t.TaskUID from dbo.MSP_EpmTask_UserView T Inner Join MSP_EpmProject_UserView P ON P.ProjectUID = T.ProjectUID INNER JOIN MSP_EpmAssignmentByDay_UserView AU ON AU.TaskUID = t.TaskUID INNER JOIN MSP_EpmAssignment_UserView A ON A.AssignmentUID = AU.AssignmentUID AND A.TaskUID = T.TaskUID where T.TaskIsProjectSummary = 0 AND T.TaskIsSummary = 0 AND T.TaskIsActive = 1 AND T.TaskIsMilestone = 0 AND P.[Statut de projet] = 'En cours' AND P.[Priorité de projet] BETWEEN '1' and '4'

    --------_UNASSIGN RESOURCE UID --------

    AND a.ResourceUID = 'B067CE64-9588-4B18-BF54-3491F76419C9' --AND AU.TimeByDay BETWEEN -- CONVERT(DATETIME, DATEADD(WEEK, - 1, CURRENT_TIMESTAMP), 102) -- AND CONVERT(DATETIME, DATEADD(month, 12, CURRENT_TIMESTAMP), 102) --and exists --(select * --from dbo.MSP_EpmAssignment_UserView A --where a.ResourceUID = 'B067CE64-9588-4B18-BF54-3491F76419C9') ----A.TaskUID = T.TaskUID) ORDER BY p.ProjectName,t.TaskIndex

    Now, I need to insert this in my first query... not easy!

    Thanks :)


    PWAdmin


    • Edited by PWAdmin Thursday, June 13, 2013 4:20 PM
    Thursday, June 13, 2013 4:19 PM