EPM Central Project Server Tutorials
Home Deployment    MS Project PWA SharePoint Articles Administrators FAQs Consulting Search Site Map
  Error Messages

Project Server 2003

PWA Login DropDown

Inactive vs. Delete

Actual Work Protected

  2003 SQL Queries

  Enterprise Fields
    Project Fields
    Resource Fields
    Sample Queries

Admin Tools

Re-associate Site

Project Archival

  VBA

 
 
 
Administration -> MS Project Server 2003 -> SQL Queries
Resource Usage (Timephase data by week)

This view generates the details of Resource Name, Project Name, Work, Actual Work, Assignment Date for all the projects in MS Project Server for specified given date intervals. This view also displays the Week Start date for each day. Week Start date is calculated as Monday of that assignment day.

This scripts generates the data same as Resource Usage View in project plan.

Please specify the your required date ranges for @StartDt and @EndDt


DECLARE
@StartDt As DateTime,
@EndDt As DateTime

SET @StartDt = '1/1/2007'
SET @EndDt = '12/1/2007'

SELECT dbo.MSP_WEB_PROJECTS.PROJ_NAME AS PROJ_NAME,
dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart AS ASSN_DAY,
CASE
WHEN DATEPART(dw, dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart) = 1 Then
DATEADD(dd,-6,dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart)
WHEN DATEPART(dw, dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart) = 3 Then
DATEADD(dd,-1,dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart)
WHEN DATEPART(dw, dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart) = 4 Then
DATEADD(dd,-2,dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart)
WHEN DATEPART(dw, dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart) = 5 Then
DATEADD(dd,-3,dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart)
WHEN DATEPART(dw, dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart) = 6 Then
DATEADD(dd,-4,dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart)
WHEN DATEPART(dw, dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart) = 7 Then
DATEADD(dd,-5,dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart)
ELSE dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart
END AS WK_START,
ROUND(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeRegularWork /
60000,0) AS REG_WRK,
ROUND(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeActualWork / 60000,0) AS ACTUAL_WRK,
dbo.MSP_RESOURCES.RES_NAME AS RES_NAME,
CASE
WHEN dbo.MSP_RESOURCES.RES_EUID IS NULL THEN 'Local'
WHEN dbo.MSP_RESOURCES.RES_EUID > 0 THEN 'Enterprise'
END AS RES_TYPE,
CASE
WHEN dbo.MSP_RESOURCES.RES_EUID > 0 AND
dbo.MSP_RESOURCES.RES_RTYPE = 0 THEN 'Real'
WHEN dbo.MSP_RESOURCES.RES_EUID > 0 AND
dbo.MSP_RESOURCES.RES_RTYPE = 1 THEN 'Generic'
END AS GENERIC,
dbo.MSP_TASKS.TASK_NAME AS TASK_NAME, dbo.MSP_TASKS.TASK_PCT_COMP AS PCT_COMP,
dbo.MSP_TASKS.TASK_PCT_WORK_COMP AS PCT_WRKCOMP
FROM dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY INNER JOIN
dbo.MSP_ASSIGNMENTS ON
dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.ProjectUniqueID = dbo.MSP_ASSIGNMENTS.PROJ_ID AND
dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentUniqueID = dbo.MSP_ASSIGNMENTS.ASSN_UID INNER JOIN
dbo.MSP_RESOURCES ON dbo.MSP_ASSIGNMENTS.PROJ_ID =
dbo.MSP_RESOURCES.PROJ_ID AND
dbo.MSP_ASSIGNMENTS.RES_UID = dbo.MSP_RESOURCES.RES_UID INNER JOIN
dbo.MSP_TASKS ON dbo.MSP_ASSIGNMENTS.PROJ_ID = dbo.MSP_TASKS.PROJ_ID AND
dbo.MSP_ASSIGNMENTS.TASK_UID = dbo.MSP_TASKS.TASK_UID INNER JOIN
dbo.MSP_WEB_PROJECTS ON dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.WPROJ_ID = dbo.MSP_WEB_PROJECTS.WPROJ_ID
WHERE (dbo.MSP_RESOURCES.RES_NAME IS NOT NULL) AND
(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart >= @StartDt
AND dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart <= @EndDt)




Copyright 2013. EPM Central. All rights reserved.