Query to find Projects with summary level Resource assignment

Environment: Project Server 2010

Requirement: Recently, we have got a requirement as part of EPM system health check data diagnostic for one of our client. For which, I have made a below small SQL query to detect the projects those are having the summary resource assignments. Hope this helps you.

–Query to display the Project Names having Summary Level resource Assignment

Select distinct P.ProjectName, COUNT(a.AssignmentUID)

FROM

MSP_EpmProject p INNER JOIN MSP_EpmTask t

ON p.ProjectUID=t.projectuid

INNER JOIN MSP_EpmAssignment A on t.taskUID=A.TaskUID

WHERE  t.TaskIsSummary=1

GROUP By P.ProjectName, t.taskuid

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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

%d bloggers like this: