Project Planning

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.

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

Programatically re-ordering a "DisplayOrder" column

Anyone know of any resources or information on writing a query that would accept a new "DisplayOrder" for a particular row and re-order the column for all other rows?

Thanks in advance!If you are asking the question I think you are asking, I had posted an answer to thishere.

The idea is to bump up the sequence number for each record that has a sequence number greater than or equal to the new sequence number...BUT only if the sequence number already is on file.

IF EXISTS(SELECT NULL FROM myTable WHERE sequence = @.NewSequence)
 BEGIN
 UPDATE myTable SET sequence = sequence + 1 WHERE sequence >= @.NewSequence
 END
UPDATE myTable SET sequence = @.NewSequence WHERE id = @.idToChange

Terri|||Thank You! A few questions...

IF EXISTS(SELECT NULL FROM myTable WHERE sequence = @.NewSequence)

This tests for the existence of the "@.NewSequence" value?
And what exactly is achieved by "SELECT NULL", I haven't seen that before?

I think this will work well for me...

Also, the sequence number must exist as it is selected from a DropDownList populated from the existing sequence.

Again, Thank You!|||The "SELECT NULL" works the same as "SELECT *" or "SELECT columnname" -- the column(s) selected have no bearing on the success of the EXISTS test. I tend to use NULL because I believe that requires the least amount of resources to process. Others choose to use the primary key column. I am honestly not sure how much of a difference there really is.

All that code is doing is checking to see if the new sequence number is already on file. If it is, then it pushes down all of the sequence numbers from that point forward to create a "space".

I'm glad you could make use of the method :-)

Terri