|
|
| |
|
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)
| |
|
|
