Posted by Erland Sommarskog on 02/13/07 22:25

PP (paul@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]