Project Planning

[PP]

Hello

Based on three tables (Projects, Tasks and UserCalender) I would like
to work out the total amount of available resources (UserCalender
table contains a entry for each user for each day, day being 7.5
hours) and total required effort (sum of Tasks.EstimateLikley) split
over 12 months.

For example:
Jan:
Available Resources: (4 Users, 7.5 hours per day, 5 working days per
week, 23 Working days in Jan) = (23 x 4) = (92 * 7.5) = 690 Available
Hours
Required Resources:
Project Start Date: 1/1/2007
Project End Date: 1/6/2007
Total Required effort (Sum of Tasks.Hours for above project): 500
Hours Average over 6 months = 83.33 Hours per month, so in Jan I need
to deduct 83.33 from 600 = 516.67 Hours.

etc

How could I do this, I have tried several ways but finding it hard.

Thanks

[Erland Sommarskog]

PP (pa**@bobbob.net) writes:

Based on three tables (Projects, Tasks and UserCalender) I would like
to work out the total amount of available resources (UserCalender
table contains a entry for each user for each day, day being 7.5
hours) and total required effort (sum of Tasks.EstimateLikley) split
over 12 months.

For example:
Jan:
Available Resources: (4 Users, 7.5 hours per day, 5 working days per
week, 23 Working days in Jan) = (23 x 4) = (92 * 7.5) = 690 Available
Hours
Required Resources:
Project Start Date: 1/1/2007
Project End Date: 1/6/2007
Total Required effort (Sum of Tasks.Hours for above project): 500
Hours Average over 6 months = 83.33 Hours per month, so in Jan I need
to deduct 83.33 from 600 = 516.67 Hours.

etc

How could I do this, I have tried several ways but finding it hard.

Is this disguise for your real business problem? I mean, project-
planning tools are plentiful on the market, so I don't see why you
would write your own. Or is it a class assignment?

In any case, for this types of questions, it helps if you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This helps to clarify ambiguities in your post, and it also makes it
easy to copy and past to develop a tested solution.
.... although, if it's really a class assignment, you are better to
discuss the problem with your teacher. You are likely to learn more
that way.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx