E
E. Froma
After extensively searching the web for sql-examples on how to extract
timephase data from Project database tables I found that there was very
little sample material around. It took me hours of work to construct a query
to extract the timephase data for a given assignment. I hope this example
will help others trying to achieve the same.
To use this query simply save a project (2003) as an access-database. (Be
sure the option "Expand timephased data in the database" on the
Tools/Options-menu is checked.) This example makes use of the custom field
"Resource Code" which can be accessed in the "Resource View" of Project. With
this query the completed work (=Assignment Actual Work) for a specific task
and resource are determined for a specific week. Replace the value "310" in
the query with your own valid Resource Code and the value "161" with a valid
TaskID. You may also want to change the dates. Create a new query in Access,
click on the SQL-view and paste the query in the window. Execute the query.
To see a graphical presentation of the links of the tables simply click on
the Design-view in Access. The query:
SELECT
SUM((IIf(IsNull([TD_VALUE1]),0,[TD_VALUE1])+IIf(IsNull([TD_VALUE2]),0,[TD_VALUE2])+IIf(IsNull([TD_VALUE3]),0,[TD_VALUE3])+IIf(IsNull([TD_VALUE4]),0,[TD_VALUE4])+IIf(IsNull([TD_VALUE5]),0,[TD_VALUE5])+IIf(IsNull([TD_VALUE6]),0,[TD_VALUE6])+IIf(IsNull([TD_VALUE7]),0,[TD_VALUE7])))/60000 AS [Completed Work (h)]
FROM MSP_CONVERSIONS AS MSP_CONVERSIONS_1 INNER JOIN (MSP_TEXT_FIELDS INNER
JOIN (((MSP_TASKS INNER JOIN (MSP_RESOURCES INNER JOIN (MSP_ASSIGNMENTS INNER
JOIN MSP_TIMEPHASED_DATA ON (MSP_ASSIGNMENTS.PROJ_ID =
MSP_TIMEPHASED_DATA.PROJ_ID) AND (MSP_ASSIGNMENTS.ASSN_UID =
MSP_TIMEPHASED_DATA.TD_REF_UID)) ON (MSP_RESOURCES.PROJ_ID =
MSP_ASSIGNMENTS.PROJ_ID) AND (MSP_RESOURCES.RES_UID =
MSP_ASSIGNMENTS.RES_UID)) ON (MSP_TASKS.PROJ_ID = MSP_ASSIGNMENTS.PROJ_ID)
AND (MSP_TASKS.TASK_UID = MSP_ASSIGNMENTS.TASK_UID)) INNER JOIN
MSP_CONVERSIONS ON MSP_TIMEPHASED_DATA.TD_FIELD_ID =
MSP_CONVERSIONS.CONV_VALUE) INNER JOIN MSP_STRING_TYPES ON
MSP_CONVERSIONS.STRING_TYPE_ID = MSP_STRING_TYPES.STRING_TYPE_ID) ON
(MSP_TEXT_FIELDS.PROJ_ID = MSP_ASSIGNMENTS.PROJ_ID) AND
(MSP_TEXT_FIELDS.TEXT_REF_UID = MSP_RESOURCES.RES_UID)) ON
MSP_CONVERSIONS_1.CONV_VALUE = MSP_TEXT_FIELDS.TEXT_FIELD_ID
WHERE (((MSP_CONVERSIONS.CONV_VALUE)=2 Or (MSP_CONVERSIONS.CONV_VALUE)=3)
AND ((MSP_TEXT_FIELDS.TEXT_VALUE)="310") AND
((MSP_TIMEPHASED_DATA.TD_CATEGORY)=3) AND
((MSP_CONVERSIONS_1.CONV_STRING)="Resource Code") AND
((MSP_STRING_TYPES.STRING_TYPE)="Timephased Contour Type") AND
((MSP_TASKS.TASK_ID)=161) AND ((MSP_TIMEPHASED_DATA.TD_START)>=#8/16/2004#)
AND ((MSP_TIMEPHASED_DATA.TD_FINISH)<=#8/22/2004 23:59:59#));
To extract timephase data for a resource without regard to a specific task
remove the constraints on the task number in the WHERE-clause of the query.
To determine Remaining work for the assignment simply replace
((MSP_CONVERSIONS.CONV_VALUE)=2 Or (MSP_CONVERSIONS.CONV_VALUE)=3) with
(MSP_CONVERSIONS.CONV_VALUE)=1
Possible values for CONV_VALUE :
1 Assignment remaining work
2 Assignment actual work
3 Assignment actual overtime work
4 Assignment baseline work
5 Assignment baseline cost
6 Assignment actual cost
7 Resource baseline work
8 Resource baseline cost
9 Task baseline work
10 Task baseline cost
11 Task percent complete
Possible values for TD_CATEGORY:
0 Task
1 Resource
3 Assignment
For further information on timephase data search your computer (or the
internet) for the following documents: pjdb.htm, projdb.htm, prjoledb.htm
If you find this query helpful please post a reply on this topic.
Sincerely,
Edze Froma.
timephase data from Project database tables I found that there was very
little sample material around. It took me hours of work to construct a query
to extract the timephase data for a given assignment. I hope this example
will help others trying to achieve the same.
To use this query simply save a project (2003) as an access-database. (Be
sure the option "Expand timephased data in the database" on the
Tools/Options-menu is checked.) This example makes use of the custom field
"Resource Code" which can be accessed in the "Resource View" of Project. With
this query the completed work (=Assignment Actual Work) for a specific task
and resource are determined for a specific week. Replace the value "310" in
the query with your own valid Resource Code and the value "161" with a valid
TaskID. You may also want to change the dates. Create a new query in Access,
click on the SQL-view and paste the query in the window. Execute the query.
To see a graphical presentation of the links of the tables simply click on
the Design-view in Access. The query:
SELECT
SUM((IIf(IsNull([TD_VALUE1]),0,[TD_VALUE1])+IIf(IsNull([TD_VALUE2]),0,[TD_VALUE2])+IIf(IsNull([TD_VALUE3]),0,[TD_VALUE3])+IIf(IsNull([TD_VALUE4]),0,[TD_VALUE4])+IIf(IsNull([TD_VALUE5]),0,[TD_VALUE5])+IIf(IsNull([TD_VALUE6]),0,[TD_VALUE6])+IIf(IsNull([TD_VALUE7]),0,[TD_VALUE7])))/60000 AS [Completed Work (h)]
FROM MSP_CONVERSIONS AS MSP_CONVERSIONS_1 INNER JOIN (MSP_TEXT_FIELDS INNER
JOIN (((MSP_TASKS INNER JOIN (MSP_RESOURCES INNER JOIN (MSP_ASSIGNMENTS INNER
JOIN MSP_TIMEPHASED_DATA ON (MSP_ASSIGNMENTS.PROJ_ID =
MSP_TIMEPHASED_DATA.PROJ_ID) AND (MSP_ASSIGNMENTS.ASSN_UID =
MSP_TIMEPHASED_DATA.TD_REF_UID)) ON (MSP_RESOURCES.PROJ_ID =
MSP_ASSIGNMENTS.PROJ_ID) AND (MSP_RESOURCES.RES_UID =
MSP_ASSIGNMENTS.RES_UID)) ON (MSP_TASKS.PROJ_ID = MSP_ASSIGNMENTS.PROJ_ID)
AND (MSP_TASKS.TASK_UID = MSP_ASSIGNMENTS.TASK_UID)) INNER JOIN
MSP_CONVERSIONS ON MSP_TIMEPHASED_DATA.TD_FIELD_ID =
MSP_CONVERSIONS.CONV_VALUE) INNER JOIN MSP_STRING_TYPES ON
MSP_CONVERSIONS.STRING_TYPE_ID = MSP_STRING_TYPES.STRING_TYPE_ID) ON
(MSP_TEXT_FIELDS.PROJ_ID = MSP_ASSIGNMENTS.PROJ_ID) AND
(MSP_TEXT_FIELDS.TEXT_REF_UID = MSP_RESOURCES.RES_UID)) ON
MSP_CONVERSIONS_1.CONV_VALUE = MSP_TEXT_FIELDS.TEXT_FIELD_ID
WHERE (((MSP_CONVERSIONS.CONV_VALUE)=2 Or (MSP_CONVERSIONS.CONV_VALUE)=3)
AND ((MSP_TEXT_FIELDS.TEXT_VALUE)="310") AND
((MSP_TIMEPHASED_DATA.TD_CATEGORY)=3) AND
((MSP_CONVERSIONS_1.CONV_STRING)="Resource Code") AND
((MSP_STRING_TYPES.STRING_TYPE)="Timephased Contour Type") AND
((MSP_TASKS.TASK_ID)=161) AND ((MSP_TIMEPHASED_DATA.TD_START)>=#8/16/2004#)
AND ((MSP_TIMEPHASED_DATA.TD_FINISH)<=#8/22/2004 23:59:59#));
To extract timephase data for a resource without regard to a specific task
remove the constraints on the task number in the WHERE-clause of the query.
To determine Remaining work for the assignment simply replace
((MSP_CONVERSIONS.CONV_VALUE)=2 Or (MSP_CONVERSIONS.CONV_VALUE)=3) with
(MSP_CONVERSIONS.CONV_VALUE)=1
Possible values for CONV_VALUE :
1 Assignment remaining work
2 Assignment actual work
3 Assignment actual overtime work
4 Assignment baseline work
5 Assignment baseline cost
6 Assignment actual cost
7 Resource baseline work
8 Resource baseline cost
9 Task baseline work
10 Task baseline cost
11 Task percent complete
Possible values for TD_CATEGORY:
0 Task
1 Resource
3 Assignment
For further information on timephase data search your computer (or the
internet) for the following documents: pjdb.htm, projdb.htm, prjoledb.htm
If you find this query helpful please post a reply on this topic.
Sincerely,
Edze Froma.
