Timephased Query

[AJ]

I am trying to extract weekly data from
tasktimephasedbyweek table. I joined
resourcetimephasedbyweek table with tasktimephasedbyweek
to get Actual, Baseline and Remaining hours for an
individual. The where condition has task uinique ID and
resoure unique ID. My query returns duplicate records. Is
there any field I am missing in my join??

Please help.

 

[JackD]

Isn't there an assignments timephased data?

-Jack

 

[AJ]

Do I need to join the assignmenttimephasedbyweek table??
If I do then what key I need to join among all the three
tables. I also like to know what does a 7 digit number
field called under the unique ID field because I think if
I can query based on this, I would get the right data.

Thanks
Ajay

 

[JackD]

You only need assignmenttimephasedbyweek table. You do not need the other
two tables.
Then join to the assignments table using AssignmentUniqueID.
From there you can get resource ID, resource name, task name, task ID etc.

-Jack

 

[AJ]

Thanks for your help but I am trying to get weekly data on
Actual Hours, Baseline Hours, Remaining Hours and Work
Hours for a particular resource for a given task. I
thought that the tasktimephasedbyweek table has the
pertinent data. I cross checked the data in
assignmenttimephasedbyweek table with the project plan,
somehow they both don't match. My manager wants to see all
these hours as shown on the project plan, in a database so
that he can query on any resource for any task.
Please help.

Thanks

 

[JackD]

No. The tasktimephased table is not right. Use the assignmenttiimephased
table.
An assignment is a particular resources work on a particular task.

A task may have more than one resource, so the task table is not a good
source.
In fact, in the task table there is no reference to which work was done by a
particular resource.
The only time it will be correct is when there is only one resource on the
task.

The resource timephased table is similar. It shows the total time for a
resource, but not for any particular task.

So, the only place you can get this data is using the assignment table. You
will have to figure out why it doesn't match your plan. It SHOULD match.
What sort of discrepancy are you seeing?
Start with a simple plan and see if you can figure out why it is not
working.

-Jack

 

[AJ]

Hi,
I am back again. I was so happy about the data but I guess
something is wrong somewhere please help.
Here is the situation:
Resource Name Work Baseline Work
SI_Cont4(McKay) 161.3 hrs 181.5 hrs
Remaining Work Actual Work
131.8 hrs 29.5 hrs
This what I have in the project plan. I joined
assignmenttiimephasedbyweek and assignments table to get
rest of the task ID, Resource ID, Resource Name, Task Name
etc. I do not get the right result. What I get is

Resource Name Work Baseline Work
SI_Cont4(McKay) 1.5 hrs 1.5 hrs
Remaining Work Actual Work
131.8 hrs 1.5 hrs
only Remaining Work is correct. What am I doing wrong
here. Please help.

Thanks
AJ

 

[JackD]

Remember it is timescaled so you need to get all of the timephased data for
that assignment. That will be multiple records for a task which is more than
a week long.
Add them together and see what you come up with.

-Jack

 

[AJ]

Never mind Jack. But I must thank you again. Actually
Assignment Unique ID was missing in Assignment table and
that is why the resultset was not accurate in the join. I
figured it out.
Thanks again.
AJ

 

[JackD]

Can you post the working code as an example here? It might help people who
are trying to do this in the future.

-Jack

 

[AJ]

Hi Jack,
I will post the code in a day or two. I apologise it has
been crazy here. Could you please tell me why I always get
Project UniqueID as -1 for any number of projects I
extract data from.

Thanks
Aj

 

[JackD]

I'm not sure why. I seem to think that at one time a long time ago I might
have known the answer, but I haven't been working with the database for a
while.

-Jack

 

[Aj]

Hi,
Do you know how can I distinguish data from multiple plans
in a database if I don't have any unique ID??? I mean if I
extract assignment timephase data from two plans, how do I
build any query if I can't provide any kind of unique key
in the query. Please help.

Thanks

 

[JackD]

You need to supply a unique ID, but if you store the plans in the database,
project will ensure that the project ID is unique.

-Jack