Calculating Earned Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calculating Earned Value

Author

Topic

iancuct
Yak Posting Veteran

73 Posts

Posted - 2002-10-09 : 10:50:13

Hello sql gurus
WeekEndingDate EarnedValue SubTaskNumber
9/1/2002 $334.40 1
9/29/2002 $352.00 1
9/29/2002 $440.00 2
9/29/2002 $22.60 4
10/27/2002 $704.00 2
10/27/2002 $16.80 3
10/27/2002 $452.00 4
12/1/2002 $880.00 2
12/1/2002 $336.00 3

I need to write a query that would append the max earned value
for a given subtask to weekending dates where that subtask does not
exist:

Example
10/27/2002 $352.00 1
12/1/2002 $452.00 4

iancuct
Yak Posting Veteran

73 Posts

Posted - 2002-10-09 : 12:02:54

sorry for the cross post, i had to refresh because the page wasn't displaying.

1fred
Posting Yak Master

158 Posts

Posted - 2002-10-09 : 12:07:04

This is working but you have do to it for each date, just put all your date in a temp table(id,date) and loop through
your id and replace the date in my query with the variable.

select b.earnedValue,b.subtaskNumber from table1,(
select table1.weekendingDate,table1.earnedValue,table1.subtaskNumber
from table1,
(select SubtaskNumber,max(earnedValue) as earnedValue from table1
group by SubtaskNumber) a
where a.SubtaskNumber = table1.SubtaskNumber
and a.earnedvalue = table1.earnedValue
group by table1.weekendingDate,table1.earnedValue,table1.subtaskNumber)b
where table1.weekendingdate = '2002-12-01' and b.subtaskNumber not in(select subtaskNumber from table1 where table1.weekendingdate = '2002-12-01')
group by b.earnedValue,b.subtaskNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources