Hi,
I'm using MPS 2003 and would am looking for a table(s) that would allow me
to query actuals as they appear in the adjust actuals view.
Thanks,
- James
Hi James,
Run the following SQL script on SQL query analyzer. You will get the
entire data " resource name, resource type, project name, date, work,
actual work" etc.
Please set your period at the begining of the query as per your need.
(Start Dt, End Dt)
**************************************************************************************************
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)
************************************************************************************************************
Thanks
Chak
