SQL Task Planner

[VVVA]

I'm working on a mini-scheduler, and I'm not sure how to do what I want to do with SQL. I think it'll be easiest to show the data I've got and what I want...

Task	                ManHours     Person  Pri
Clean up the room       16           Bob     2
Clean up the room       16           Lucy    1
Clean up the room       8            George  2
Feed the dog	        8            George  1
Take out the trash      16           Bob     1
Take out the trash      16           Lucy    1

From this, I want to get...

Task                 DaysToCompletion
Clean up the room    2
Feed the Dog         1
Take Out the Trash   1

P.S. If you want the data...

select
  'Take out the trash' as task,'16' as manhours,'Bob' as person,'1' as priority into #tmp union
select
  'Clean up the room','16','Bob','2' union
select
  'Clean up the room','16','Lucy','1' union
select
  'Take out the trash','16','Lucy','1' union
select
  'Feed the dog','8','George','1' union
select
  'Clean up the room','8','George','2'

 

[bborissov]

SELECT Task, MAX(Pri) AS DaysToCompletion
       FROM ...
GROUP BY Task

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[VVVA]

I don't think that's going to work...the result has to be based on the priority, the man hours it's going to take, and who else might be working on it...

IE

Build a doghouse      24           John    1

Should be 3 days, not one...

Build a doghouse      20           John   1
Feed the Dog          4            Larry  1
Build a doghouse      20           Larry  2
Build a doghouse      20           Joe    1

Should be one day, two people will be working on building a doghouse a full day, and one guy will be working on it half a day