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.
ThanksPP (paul@.bobbob.net) writes:
Quote:
Originally Posted by
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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
